CODEWITHSUNDEEP
mysqlsql
Dhanush Bala
Dhanush Bala

Reputation: 1132

add sum of all values mysql query

  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

Upvotes: 1

Views: 62

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

TJ-
TJ-

Reputation: 14363

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.

Upvotes: 2

Related Questions