SQL Count(*) and Group By – Find Difference Between Rows

sql

Below is a SQL query I wrote to find the total number of rows by each Product ID (proc_id):

SELECT proc_id, count(*)
FROM proc
WHERE grouping_primary = 'SLB'
AND   eff_date = '01-JUL-09'
GROUP BY proc_id
ORDER BY proc_id;

Below is the result of the SQL query above:

proc_id count(*)
01  626
02  624
03  626
04  624
05  622
06  624
07  624
09  624

Notice the total counts by proc_id = '01', proc_id = '03', and proc_id = '05' are different (not equal to 624 rows as the other proc_id).

How do I write a SQL query to find which proc_id rows are different for proc_id = '01', proc_id = '03', and proc_id = '05' as compared to the other proc_id?

Best Answer

First you need to define the criteria that makes '624' correct. Is it the average count(*) ? Is it the count(*) that occurs most often? Is it your favorite count(*) ?

Then you can use the HAVING clause to separate the ones that don't match your criteria:

SELECT proc_id, count(*)
FROM proc
WHERE grouping_primary = 'SLB'
AND   eff_date = '01-JUL-09'
GROUP BY proc_id
HAVING count(*) <> 624
ORDER BY proc_id;

or:

SELECT proc_id, count(*)
FROM proc
WHERE grouping_primary = 'SLB'
AND   eff_date = '01-JUL-09'
GROUP BY proc_id
HAVING count(*) <> (
  <insert here a subquery that produces the magic '624'>
 )
ORDER BY proc_id;