add sum of all values mysql query

Question

  select(sum(
       if (lower(st.c_gender) = 'male', 1, 0)) - ifnull(le.male, 0)) as male, (sum(
       if (lower(st.c_gender) = 'female', 1, 0)) - ifnull(le.female, 0)) as female, FROM_UNIXTIME(st.c_admissionDate, '%Y') as year, le.male, le.female
    from(SELECT s.*, zone.id as zoneid FROM groups s left join groups as zone ON s.parent_id = zone.id where s.id != s.parent_id) g, group_to_user u, student st
    left join(SELECT ifnull(sum(
       if (lower(l.c_gender) = 'male', 1, 0)), 0) as male, ifnull(sum(
       if (lower(l.c_gender) = 'female', 1, 0)), 0) as female, FROM_UNIXTIME(l.c_leavingDate, '%Y') as year from student l 
    where l.c_leavingDate is not null group by FROM_UNIXTIME(l.c_leavingDate, '%Y')) as le on le.year = FROM_UNIXTIME(st.c_admissionDate, '%Y') 
    where g.id = u.groupid and st.c_userId = u.userId group by FROM_UNIXTIME(st.c_admissionDate, '%Y')

the above query returns the following output ,

male    female  year    male    female
1860    657     1970    NULL    NULL
2        4      2012    NULL    NULL
3        0      2013    NULL    NULL
470     370     2014    0       1
0       365     2015    NULL    NULL
0       367     2016    NULL    NULL
1       260     2017    1       2

from the above output need to sum the before values like the following output

    male   female   year    male    female
    1860    657     1970    NULL    NULL
    1862    661     2012    NULL    NULL
    1865    661     2013    NULL    NULL
    2335    1031    2014    0       1
    2335    1396    2015    NULL    NULL
    2335    1763    2016    NULL    NULL
    2336    2023    2017    1       2

anyway to make the output like the above help me

TJ- · Accepted Answer · 2014-08-21 12:56:25Z

2

set @msum := 0;
set @fsum := 0;
select (@msum := @msum + T.male_sum) as male_sum, (@fsum := @fsum + T.female_sum) as female_sum, T.year, T.male, T.female
from (
select(sum(
       if (lower(st.c_gender) = 'male', 1, 0)) - ifnull(le.male, 0)) as male_sum, (sum(
       if (lower(st.c_gender) = 'female', 1, 0)) - ifnull(le.female, 0)) as female_sum, FROM_UNIXTIME(st.c_admissionDate, '%Y') as year, le.male, le.female
    from(SELECT s.*, zone.id as zoneid FROM groups s left join groups as zone ON s.parent_id = zone.id where s.id != s.parent_id) g, group_to_user u, student st
    left join(SELECT ifnull(sum(
       if (lower(l.c_gender) = 'male', 1, 0)), 0) as male, ifnull(sum(
       if (lower(l.c_gender) = 'female', 1, 0)), 0) as female, FROM_UNIXTIME(l.c_leavingDate, '%Y') as year from student l 
    where l.c_leavingDate is not null group by FROM_UNIXTIME(l.c_leavingDate, '%Y')) as le on le.year = FROM_UNIXTIME(st.c_admissionDate, '%Y') 
    where g.id = u.groupid and st.c_userId = u.userId group by FROM_UNIXTIME(st.c_admissionDate, '%Y')
)T;

Simplify this query but this will give you a general idea. Cannot be done in a non procedural query.

answered Aug 21, 2014 at 12:56

TJ-

14.4k13 gold badges64 silver badges92 bronze badges

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

2 Comments

Dhanush Bala Over a year ago

thank u , running in php myadmin fine , but how to use this in php

TJ- Over a year ago

If this is to be done in Php, I'd rather retrieve the data from your original query and aggregate it in the application (php). Otherwise you'll have to create a procedure to do so which I would not recommend.

Paul Maxwell · Accepted Answer · 2014-08-21 13:03:59Z

1

You really need a column for a reliable ordering.

But a method in MySQL is to use @ variables like this.

SELECT
      @male = @male + male        as male
    , @female = @female + female  as female
    , ordering_column
FROM (
       << your existing query here >>
     )
CROSS JOIN ( SELECT @male := 0 m, @female := 0 f ) vars
ORDER BY
     ordering_column

answered Aug 21, 2014 at 13:03

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

add sum of all values mysql query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related