How to select sum of count of distinct values in mysql?

Question

This is my Query :

SELECT 
    p.PRO_Id, p.PRO_Name,
    COUNT(DISTINCT c.COM_Id) as commentCount,
    COUNT(DISTINCT d.DIS_Id) AS disCount, 
    d.DIS_ThreadDesc,
    dev.DEV_Name,
    loc.LOC_Name,
    d.USE_Id,
    d.DIS_Date
FROM projects p
LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id
LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id
LEFT JOIN developer dev ON p.DEV_Id=dev.DEV_Id
LEFT JOIN locality loc ON p.LOC_Id=loc.LOC_Id
WHERE p.PRO_Status=1 
  and d.DIS_Status=1 
  and c.COM_Status=2
GROUP BY p.PRO_Id
ORDER  BY 3 desc LIMIT 3

What i'm trying to get is the sum of commenCount(total comments) and disCount(total discussions).
I tried like this;-

 sum(COUNT(DISTINCT c.COM_Id) + COUNT(DISTINCT d.DIS_Id)) AS totalResponse

but no chance.

score 4 · Accepted Answer · edited May 23 '17 at 12:24

You can add result of both COUNTs:

SELECT 
    p.PRO_Id, p.PRO_Name,
    COUNT(DISTINCT c.COM_Id) AS commentCount,
    COUNT(DISTINCT d.DIS_Id) AS disCount, 
    COUNT(DISTINCT c.COM_Id) + COUNT(DISTINCT d.DIS_Id) AS totalResponse
    d.DIS_ThreadDesc, dev.DEV_Name, loc.LOC_Name, d.USE_Id, d.DIS_Date
FROM projects p
...

alternatively using subquery:

SELECT sub.*,
       commentCount + disCount AS totalResponse
FROM (
  SELECT 
    p.PRO_Id, p.PRO_Name,
    COUNT(DISTINCT c.COM_Id) as commentCount,
    COUNT(DISTINCT d.DIS_Id) AS disCount, 
    d.DIS_ThreadDesc,
    dev.DEV_Name,
    loc.LOC_Name,
    d.USE_Id,
    d.DIS_Date
  FROM projects p
  LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id
  LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id
  LEFT JOIN developer dev ON p.DEV_Id=dev.DEV_Id
  LEFT JOIN locality loc ON p.LOC_Id=loc.LOC_Id
  WHERE p.PRO_Status=1 
   and d.DIS_Status=1 
   and c.COM_Status=2
  GROUP BY p.PRO_Id
) AS sub
ORDER BY commentCount LIMIT 3

Keep in mind that your SELECT list and GROUP BY does not match. This will work for MySQL but is not compliant with ANSI standard. You should have the same list in both clauses or use aggregate functions for columns that are not specified in GROUP BY. More info here.

@Plum: Yes. Understand that `SUM()` is intended to sum the values within a column. If you wanna add the values, think straightforward and use the humble `+`. — Sarath Chandra, Dec 24 '15 at 07:08

How to select sum of count of distinct values in mysql?

1 Answers1