MySql sum query

Question

I need a MySql query to do the following: Select all the items in a table, group them by a type, order them by count type and also sum all the count.

This is what I've done so far:

select type, count(*) as cnt from $tbl_name group by type order by count(*) desc;

This only gives me the count for each group. What should I add so that this code will also show the total count (sum the counts from every group).

June7 · Accepted Answer · 2023-01-24 21:03:33Z

1

select * from (
select type as type, 
count(*) as cnt 
from $tbl_name 
group by type WITH ROLLUP) as inner_table
order by cnt desc;

Note that the first row will be the rolled up total sum.

ROLLUP reference

edited Jan 24, 2023 at 21:03

June7

21.7k8 gold badges28 silver badges36 bronze badges

answered May 11, 2011 at 13:06

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

XCS Over a year ago

And how do I acces the value?

bpgergo Over a year ago

Sorry I edited the select cause it threw "incorrect usage of ROLLUP and ORDER BY". With what value do you have problem accessing it?

XCS Over a year ago

This query returns: Parse error: syntax error, unexpected T_VARIABLE

bpgergo Over a year ago

@Cristy, there is no T_VARIABLE in this query. I just run it (replaced table name and column name) in my MySQL db and it is just fine.

XCS Over a year ago

Sorry my bad, it works ok, but I still don't know how to get the total sum from it. Can you explain that? I mean, I get the cnt by fetching the result and accessing $row['cnt'] , but how do I get the total sum? (inner_table)

|

user744116 · Accepted Answer · 2011-05-11 13:05:36Z

0

Try this:

select type, SUM(count(*)) as cnt from $tbl_name group by type order by count(*) desc;

answered May 11, 2011 at 13:05

user744116

3 Comments

XCS Over a year ago

I need to have the cnt I've done before and also a variable showing how many items are in total.

user744116 Over a year ago

if you fetch the query into loop, you can do it there.

XCS Over a year ago

I don't do it, I only fetch the first 6 rows.

Collectives™ on Stack Overflow

MySql sum query

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related