SELECT salesman who had sales in particular years POSTGRES-CodePudding

I want to find a salesman who had sales in 1998, 1999 and 2001

For example:

**year  salesmen**
  1998        a
  1998        a
  1998        b
  1999        a
  1999        b
  1999        c
  2001        a
  2001        b
  2001        c

result should be "a" and "b" because only those salesman had sales in all years

I don't think it requires complex query but I could not come up with solution

CodePudding user response：

Aggregation is one option:

SELECT salesmen
FROM yourTable
WHERE year IN (1998, 1999, 2001)
GROUP BY salesmen
HAVING COUNT(DISTINCT year) = 3;

CodePudding user response：

This query shows salesmen that had sales in all 3 years(1998,1999,2001) at least once with only 1 read on the table:

select salesmen
from your_table
group by salesmen having count(case when year = 1998 then 1 end)>0 and
                         count(case when year = 1999 then 1 end)>0 and
                         count(case when year = 2001 then 1 end)>0

DB<>FIDDLE LINK

CodePudding user response：

There are many options to get the desired result. One is using HAVING and COUNT or GROUP BY (there are already answers that show this way). I want to show the option to do not query harcoded the 3 years, but to do this instead:

SELECT salesmen
FROM yourTable
WHERE year IN (SELECT year FROM yourtable)
GROUP BY salesmen
HAVING COUNT(DISTINCT year) = (SELECT COUNT(DISTINCT year) FROM yourtable);

If this is intended, this will be the way to select those salesmen that occur in every year that appears in your table.

To come back to your question, another possibility is to use IN:

SELECT DISTINCT salesmen FROM yourtable 
WHERE salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 1998) AND salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 1999) AND salesmen IN
(SELECT salesmen FROM yourtable WHERE year = 2001);

You can also use EXISTS:

SELECT DISTINCT salesmen FROM yourtable y 
WHERE 
EXISTS (SELECT 1 FROM yourtable WHERE year = 1998 AND salesmen = y.salesmen) AND 
EXISTS (SELECT 1 FROM yourtable WHERE year = 1999 AND salesmen = y.salesmen) AND
EXISTS (SELECT 1 FROM yourtable WHERE year = 2001 AND salesmen = y.salesmen);

You can see the difference here and try out: db<>fiddle