CODEWITHSUNDEEP
mysqlsqldatabase
user622378
user622378

Reputation: 2336

Getting total value from each field?

How do I get the Total value of Yes, No, Other fields of each username?

I like to add Total field.

SELECT Username,
  SUM(CASE WHEN type = 'Yes' THEN 1 ELSE NULL END) as Yes,
  SUM(CASE WHEN type = 'No' THEN 1 ELSE NULL END) as No,
  SUM(CASE WHEN type = '' THEN 1 ELSE NULL END) as Other  
  //How to get total of Yes/No/Other
  FROM table 
WHERE source = 'CompanyName' ";

Also the highest Total goes at the top order.

Upvotes: 1

Views: 196

Answers (3)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

use 0 instead of NULL, add the missing group by and use COUNT(*) to get the total of each group and order the result:

SELECT Username,
  SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
  SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
  SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other,  
  COUNT(*) as TOTAL
  FROM table 
WHERE source = 'CompanyName'
group by Username
order by TOTAL desc;

This assumes that type can only be 'Yes', 'No' or ''.

Upvotes: 2

Johan
Johan

Reputation: 76567

Don't use SUM(null), the SUM of (1,1,1,null) = null, not 3.

SELECT s.*, s.yes+s.no+s.other as all FROM (
  SELECT Username,
     SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
     SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
     SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other  
     FROM table 
   WHERE source = 'CompanyName'
   GROUP BY Username
) s
ORDER BY all DESC

Upvotes: 1

Keith
Keith

Reputation: 155662

Use a sub query to sum up your results and add a sort:

select yes, no, other, yes + no + other as Total
from (

    SELECT Username,
      SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
      SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
      SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other  
      FROM table 
    WHERE source = 'CompanyName'
)
order by (yes + no + other) desc

Upvotes: 1

Related Questions