CODEWITHSUNDEEP
phpmysqldistinct
rahul
rahul

Reputation: 849

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

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.

Upvotes: 1

Views: 1484

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

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.

Upvotes: 4

Related Questions