column "activiteit.straat" must appear in the GROUP BY clause or be used in an aggregate f-CodePudding

So I need to have the name as a column and then also street but it doesn't work.

I tried this

SELECT naam, straat FROM activiteit GROUP BY naam HAVING COUNT(*)>1;

expected to have this

naam | straat |         
-------- -------- 
 tennis  | Gent   |  
 basket  | Antwerpen  |

but got this

column "activiteit.straat" must appear in the GROUP BY clause or be used in an aggregate function

CodePudding user response：

This is it: You need this I guess:

select naam,straat from (
SELECT naam, straat,count(*) FROM activiteit GROUP BY naam,straat  HAVING  
COUNT(*)>1);

CodePudding user response：

We can use aggregation here:

SELECT naam, straat
FROM activiteit
WHERE naam IN (
    SELECT naam
    FROM activiteit
    GROUP BY naam
    HAVING MIN(straat) <> MAX(straat)
);

We could also use window functions:

WITH cte AS (
    SELECT *, MIN(straat) OVER (PARTITION BY naam) AS min_straat,
              MAX(straat) OVER (PARTITION BY naam) AS max_straat
    FROM activiteit
)

SELECT naam, straat
FROM cte
WHERE min_straat <> max_straat;

CodePudding user response：

If you have multiple streets for every name, yove to decide which tio take A MIN MAX or any other aggregation function will do

the following will show you all streets with the same name

SELECT naam, string_agg(istinct straat , ',') as straat
FROM activiteit 
GROUP BY naam 
HAVING COUNT(*)>1;