Sum columns in Count Case MYSQL

Question

I am trying to total up the columns in a Count Case query. I have the following query

SELECT distinct type,
Year(`date_ready`) as Year,
Count(case when month(`date_ready`)=1 then `type` end) As Jan_Attr,
Count(case when month(`date_ready`)=2 then `type ` end) As Feb_Attr,
Count(case when month(`date_ready`)=3 then `type ` end) As Mar_Attr,
Count(case when month(`date_ready`)=4 then `type ` end) As Apr_Attr,
Count(case when month(`date_ready`)=5 then `type ` end) As May_Attr,
Count(case when month(`date_ready`)=6 then `type ` end) As Jun_Attr,
Count(case when month(`date_ready`)=7 then `type ` end) As Jul_Attr,
Count(case when month(`date_ready`)=8 then `type ` end) As Aug_Attr,
Count(case when month(`date_ready`)=9 then `type ` end) As Sep_Attr,
Count(case when month(`date_ready`)=10 then `type ` end) As Oct_Attr,
Count(case when month(`date_ready`)=11 then `type ` end) As Nov_Attr,
Count(case when month(`date_ready`)=12 then `type ` end) As Dec_Attr
FROM newsuit
GROUP BY Year(`date_ready`), type

What I would like is to get a row called Total, then the total for Jan, total for Feb etc. I presume I need a Sum somewhere, but whatever I try seems to give me the total in the wrong column. I'm guessing that maybe I need to wrap the whole query in another, but I can't seem to get the syntax right.

If I put Sum(type) as Total in the select, I just get one cell with the total in. All the other things I have tried give me errors.

Cheers....

Additional discovery. I've found that the following addition before the From clause adds totals to each row. This is useful, however I still want to find the totals for the columns :)

sum(case when month(`date_ready`) <13 then 1 else 0 end) as Total

Further additional discovery....

This gives me the column totals, just need to combine the two queries.

Select Sum(PQ.Jan_Attr) As JanTot, Sum(PQ.Feb_Attr) As FebTot,Sum(PQ.Mar_Attr) As MarTot,Sum(PQ.Apr_Attr) As AprTot,Sum(PQ.May_Attr) As MayTot,Sum(PQ.Jun_Attr) As JunTot,
Sum(PQ.Jul_Attr) As JulTot,Sum(PQ.Aug_Attr) As AugTot,Sum(PQ.Sep_Attr) As SepTot,Sum(PQ.Oct_Attr) As OctTot,Sum(PQ.Nov_Attr) As NovTot ,Sum(PQ.Dec_Attr) As DecTot
From
(SELECT distinct type,
Year(`date_ready`) as Year,
Count(case when month(`date_ready`)=1 then `type` end) As Jan_Attr,
Count(case when month(`date_ready`)=2 then `type` end) As Feb_Attr,
Count(case when month(`date_ready`)=3 then `type` end) As Mar_Attr,
Count(case when month(`date_ready`)=4 then `type` end) As Apr_Attr,
Count(case when month(`date_ready`)=5 then `type` end) As May_Attr,
Count(case when month(`date_ready`)=6 then `type` end) As Jun_Attr,
Count(case when month(`date_ready`)=7 then `type` end) As Jul_Attr,
Count(case when month(`date_ready`)=8 then `type` end) As Aug_Attr,
Count(case when month(`date_ready`)=9 then `type` end) As Sep_Attr,
Count(case when month(`date_ready`)=10 then `type` end) As Oct_Attr,
Count(case when month(`date_ready`)=11 then `type` end) As Nov_Attr,
Count(case when month(`date_ready`)=12 then `type` end) As Dec_Attr,
sum(case when month(`date_ready`) <13 then 1 else 0 end) as Total
FROM newsuit
WHERE Year(`date_ready`) = '2013'
GROUP BY Year(`date_ready`), type) as PQ

UPDATE 13/02/2014 - I've solved this one now, sort of. I couldn't get what I wanted in a single query, so in the event, I've run the two queries above and just used PHP to display it all as one table. Seemed to be the simplest way in the end.

If anyone would like to know how I did this, I'll happily post / send the code I used to create the tables and format it for displaying data from multiple queries.

Answer 1

I was just asking a similar question, why not try this:

sum(case when year(`date_ready`) = '2013' then 1 else 0 end) as Total

Answer 2

How about

SELECT
  suit_type,
  Year(`date_ready`) as Year,
  SUM(IF(month(`date_ready`) = 1,`type`,0)) AS Jan_Attr,
  ...
FROM newsuit
GROUP BY Year(`date_ready`), suit_type