CODEWITHSUNDEEP
mysqlsql
alphy
alphy

Reputation: 291

Find Total Sum of a column as All at the end of mysql result

I would like to create a table like result with mysql so that i can use the data to plot in fusion charts so that i have totals in the y-axis and client_types in the x-axis, thus something like this

n+1'                     ' 
   '  '                  '
10 '  '   '          '   ' 
   '  '   '   '      '   '
0  '  '   '   '  '   '   '
   '__'___'___'__'___'___'___
      A   B   C  D  E   All

My Query as shown below returns

SELECT DISTINCT(COUNT('client_type')) AS Total, client_type FROM `clients` GROUP BY client_type

returns 

Total Client_type
 18     A
 26     B
 16     C
101     D
  2     E

SO what I want is to be able to add under client type 'All' and sum(Total) thus

Total Client_type
 18     A
 26     B
 16     C
101     D
  2     E
168     All

Suggestions

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want rollup:

SELECT COUNT(*) AS Total, coalesce(client_type, 'All')
FROM `clients`
GROUP BY client_type with Rollup;

Note: the expression DISTINCT(COUNT('client_type')) is close to non-sensical. You basically never need select distinct with a group by. The distinct applies to all the values in the select, so the parens around count('client_type') don't do anything. And, you are counting a constant, the string 'client_type', when perhaps you intend the column.

Upvotes: 2

Related Questions