Adding an aggregate condition to get total count of sub-group-CodePudding

Thanks for the help on this matter, I'm new with SQL. I'm trying to get a sub-count of Jedi who had more than 2 padawans last month. I tried putting the condition in WHERE but I get an error saying I can't include aggregates in it. I also tried using a CASE but kept getting a syntax error there too. Any help on this would be incredible. Thank you so much!

SELECT COUNT(DISTINCT old_republic.jedi_id), old_republic.region_id
FROM jedi_archives.old_repulicdata old_republic
WHERE old_republic.republic_date >= '2022-06-01' AND old_republic.republic_date <= '2022-06-30' AND  COUNT(old_republic.padawan)>2
GROUP BY old_republic.region_id
ORDER BY old_republic.region_id

SELECT old_republic.jedi_id CASE (
WHEN Count(old_republic.padawan)>2
THEN 1
ELSE 0 End), old_republic.region_id
FROM jedi_archives.old_repulicdata old_republic
WHERE old_republic.republic_date >= '2022-06-01' AND old_republic.republic_date <= '2022-06-30'
GROUP BY old_republic.region_id
ORDER BY old_republic.region_id

CodePudding user response：

I can't comment to ask for a fiddle, but from what you've written, you're probably looking for the HAVING clause.

Assuming that padawan denotes the number of Padawans:

SELECT region_id, jedi_id, sum(padawan)
  FROM jedi_archives.old_republicdata
 WHERE republic_date >= '2022-06-01' 
   AND republic_date <= '2022-06-30'
GROUP BY region_id, jedi_id
HAVING sum(padawan) > 2;

This query will return the sum of Padawans for each Jedi per region who had more than two Padawans last month in one region (if you don't want to take the region into account, remove it from the SELECT and GROUP BY clause). Other Jedis won't appear in the result.

You can use the CASE expression, too, in order to indicate whether a Jedi had more than two padawans:

SELECT region_id, jedi_id, 
       CASE WHEN sum(padawan) > 2 THEN 1 ELSE 0 END AS more_than_2_padawans 
  FROM jedi_archives.old_republicdata
 WHERE republic_date >= '2022-06-01' 
   AND republic_date <= '2022-06-30'
GROUP BY region_id, jedi_id;

CodePudding user response：

I'm not entirely sure without sample data. But I think using the HAVING clause could solve your question.

SELECT COUNT(jedi_id) as jedi_id, region_id FROM tableA
WHERE republic_date between '2022-05-20' and '2022-05-25'
GROUP BY region_id
having padawan > 2

db fiddle