mysql - SQL Query for datewise data -
i've situation need find sum of columns defined particular date. plese refer below table:
in this, need find sum of numbers date wise i.e. 15 march 15 june (in above table months denoted number e.g. 5 may , on.)
i did particular month each task below query:
select (d1+d2+d3+d4+d5+d6+d7+d8+d9+d10+d11+d12+d13+d14+d15+d16+d17+d18+d19+d20+d21+d22+d23+d24+d25+d26+d27+d28+d29+d30+d31) hoursum table1 month='<given month>';
i don't want query idea sum half or less of column values addition. thanks.
try d1 , d2
select sum(d1)+sum(d2) hoursum table1 group month,year
refer "group by" https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
updated answer:
i think table structure strange should have 1 column name date rather having 31 columns(d1-d31).
anyway, considering same table structure , data if want query data 15 march 2014 15 june 2014 need write query below: -- total sum of d1 d31
select sum(d1) +sum(d2) +sum(d3) +sum(d4) +sum(d5) +sum(d6) +sum(d7) +sum(d8)+sum(d9) +sum(d10) +sum(d11) +sum(d12) +sum(d13) +sum(d14) +sum(d15)+sum(d16) +sum(d17) +sum(d18) +sum(d19) +sum(d20)+sum(d21) +sum(d22) +sum(d23) +sum(d24) +sum(d25) +sum(d26) +sum(d27) +sum(d28) +sum(d29)+sum(d30) +sum(d31) totalhours ( -- sum 15 march 2014 31 march 2014 select 0 d1,0 d2,0 d3, 0 d5, 0 d6,0 d7, 0 d8,0 d9,0 d10,0 11,0 12,0 d13,0 d14,sum(d15) d15,sum(d16) d16,sum(d17) d17,sum(d18) d18,sum(d19) d19,sum(d20) d20,sum(d21) d21,sum(d22) d22,sum(d23) d23,sum(d24) d24,sum(d25) d25,sum(d26) d26,sum(d27) d27,sum(d28) d28,sum(d29) d29,sum(d30) d30,sum(d31) d31 table1 month = 3 , year=2014 group month )t1 union ( -- sum 1 april 2014 31 may 2014 select sum(d1) d1,sum(d2) d2,sum(d3) d3,sum(d4) d4,sum(d5) d5,sum(d6) d6,sum(d7) d7,sum(d8) d8,sum(d9) d9,sum(d10) d10,sum(d11) d11,sum(d12) d12,sum(d13) d13,sum(d14) d14,sum(d15),sum(d15) d15,sum(d16) d16,sum(d17) d17,sum(d18) d18,sum(d19) d19,sum(d20) d20,sum(d21) d21,sum(d22) d22,sum(d23) d23,sum(d24) d24,sum(d25) d25,sum(d26) d26,sum(d27) d27,sum(d28) d28,sum(d29) d29,sum(d30) d30,sum(d31) d31 table1 month in (4,5) , year=2014 group month )t2 union ( -- sum 1 june 2014 15 june 2014 select sum(d1) d1,sum(d2) d2,sum(d3) d3,sum(d4) d4,sum(d5) d5,sum(d6) d6,sum(d7) d7,sum(d8) d8,sum(d9) d9,sum(d10) d10,sum(d11) d11,sum(d12) d12,sum(d13) d13,sum(d14) d14,sum(d15),sum(d15) d15, 0 d16,0 d17, 0 d18,0 d19, 0 d20, 0 d21, 0 d22,0 d23,0 d24,0 d25,0 d26,0 d27,0 d28,0 d29,0 30,0 31 table1 month = 6 , year=2014 group month )t3
again, change table structure if possible..
Comments
Post a Comment