MySQL: Sum () and Count in one query?

Question

I'm looking for help using sum() and count () in my SQL query:

select s1_s2,count(s1_s2) as RES 
from city_user 
where s1_s2 in (select s1_s2 from city_user ) 
group by s1_s2

result: Image Query Result

I want to make sum () for RES column

Is that possible?

How exactly do you think text entries "01-02" can be summed? — Sumurai8
– Sumurai8, Commented Jul 9, 2016 at 14:32
That makes little sense to me. Unless OP just wants to count how many rows are in the database. Which is just SELECT COUNT(*) FROM city_user;. — Sumurai8
– Sumurai8, Commented Jul 9, 2016 at 14:34
I think you are trying to get the number of paths between two points A and B like paths between two nodes in the graph.. Am I right? Else can you please elaborate a little bit on this like what you would like to achieve with the query.. — Tom Taylor
– Tom Taylor, Commented Jul 9, 2016 at 15:17

Sumurai8 · Accepted Answer · 2016-07-09 16:34:05Z

1

To select the count of individual s1_s2 values the following query may help

select s1_s2, count(s1_s2) as res 
from city_user 
group by s1_s2;

To obtain the sum of res column the following query may help

select sum(res) as sum 
from 
    (select s1_s2, count(s1_s2) as res 
     from city_user 
     group by s1_s1) 
city_user;

which is the same as

select count(s1_s2) as count 
from city_user;

Reason: It's because you are trying to group the column based on s1_s2 and obtain its count and store it as Res column and calculate its sum which is same as the number of rows for s1_s2 in the table.

edited Jul 9, 2016 at 16:34

Sumurai8

20.8k11 gold badges70 silver badges103 bronze badges

answered Jul 9, 2016 at 15:49

Tom Taylor

3,6182 gold badges41 silver badges75 bronze badges

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

2 Comments

Sumurai8 Over a year ago

select distinct(s1_s2) will not prevent selection of null, which OP's nested query seemed to want to accomplish. select distinct(s1_s2) with group by s1_s2 will obviously not make a difference, because if two values for s1_s2 are the same, they should be grouped...

Tom Taylor Over a year ago

Yeah !! You are right @Sumurai8. Have edited my answer.

Abdes · Accepted Answer · 2016-07-11 19:06:32Z

1

thanks for your answers

this what I wanted:

look this pic : result

this query works fine for me:

select s1_s2,
count(s1_s2) as res,
(select sum(res) as sum from (
  select s1_s2, count(s1_s2) as res from city_user group by s1_s2) city_user) as Sum_RES from city_user  group by s1_s2

answered Jul 11, 2016 at 19:06

Abdes

1,0061 gold badge16 silver badges29 bronze badges

Comments

Rahul · Accepted Answer · 2016-07-09 14:32:19Z

0

Probably you can use a outer query like below, again your WHERE condition makes no sense as your IN clause takes all values of column s1_s2 from same table which will always be true

select *, SUM(RES) as SUM_RES
FROM (
select s1_s2,
count(s1_s2) as RES 
from city_user 
group by s1_s2) xxx

answered Jul 9, 2016 at 14:32

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Sumurai8 · Accepted Answer · 2016-07-09 14:44:36Z

0

Your query makes little sense. where s1_s2 in (select s1_s2 from city_user ) is just a complicated and REALLY inefficient way of writing where s1_s2 is not null.

The sum of the RES column is basically the count of all rows in city_user, or at least those rows in city_user for which s1_s2 is not null. For the sake of people that need to read your queries in the future, including yourself, make it two queries:

select s1_s2,count(s1_s2) as RES 
from city_user 
where s1_s2 is not null
group by s1_s2

and

select count(*)
from city_user
where s1_s2 is not null

answered Jul 9, 2016 at 14:44

Sumurai8

20.8k11 gold badges70 silver badges103 bronze badges

Collectives™ on Stack Overflow

MySQL: Sum () and Count in one query?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related