James Wilson James Wilson - 3 months ago 8
SQL Question

How to get a count when using group by and having

My query below is returning me 31 rows of '

1
'. Even tho I am doing a
COUNT()
on it.

I take out the
COUNT()
and it returns me 31 rows of the status_number. so it is returning the correct amount of results that I am expecting, but how do I force it to return 31 instead of 31 rows of '
1
'.

SELECT
COUNT(DISTINCT d.status_number)
FROM dealers d
INNER JOIN contracts c ON d.status_number= c.status_number
WHERE d.iagent_id = 79 AND d.status_code IN ('A', 'R')
AND d.status_number NOT LIKE '%demo%'
AND c.sale_date > DATEADD(MONTH, -4, GETDATE())
GROUP BY d.status_number
HAVING COUNT(DISTINCT c.sale_date ) >= 5


Currently Returning:

(No column name)
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1


Need it to return:

(No column name)
31


Table d
is a list of users, status_number is their unique identifying number within the system. status_code is whether they are active/inactive etc.

table c
is a list of contract sales. I am trying to only return a count of users who have sold 5 or more contracts in the past 4 months.

The query returning 31 is the correct result.

Answer

I am guessing that you want something like this:

SELECT COUNT(*)
FROM (SELECT d.status_number
      FROM dealers d INNER JOIN
           contracts c
           ON d.status_number = c.status_number
      WHERE d.iagent_id = 79 AND d.status_code IN ('A', 'R') AND
            d.status_number NOT LIKE '%demo%' AND
            c.sale_date > DATEADD(MONTH, -4, GETDATE())
      GROUP BY d.status_number
      HAVING COUNT(DISTINCT c.sale_date ) >= 5 
     ) cd;

That is, you need two aggregations, so you need to do two aggregations, using either a subquery, CTE, or some related construct. Your query does not need COUNT(DISTINCT) in the outer query because status_number is already distinct because of the GROUP BY.