mysql> select * from store; +------+-------+-------+ | id | month

mysql> select * from store;
+------+-------+-------+
| id | month | sales |
+------+-------+-------+
| 1 | 1 | 100 |
| 1 | 2 | 100 |
| 1 | 3 | 200 |
| 1 | 4 | 300 |
| 1 | 5 | NULL |
| 1 | 6 | 200 |
| 1 | 7 | 800 |
| 1 | 8 | 100 |
| 1 | 9 | 240 |
| 1 | 10 | 140 |
| 1 | 11 | 400 |
| 1 | 12 | 300 |
| 2 | 1 | 300 |
| 2 | 2 | 300 |
| 2 | 3 | 300 |
| 2 | 4 | 200 |
| 2 | 5 | 200 |
| 2 | 6 | 200 |
| 2 | 7 | 100 |
| 2 | 8 | 100 |
| 2 | 9 | 300 |
| 2 | 10 | 100 |
| 2 | 11 | 150 |
| 2 | 12 | 150 |
+------+-------+-------+

this is my table. i need to display output like this.

+------+----------+----------+----------+----------+
| id | quarter1 | quarter2 | quarter3 | quarter4 |
+------+----------+----------+----------+----------+
| 1 | 400 | 500 | 1140 | 840 |
| 2 | 900 | 600 | 500 | 400 |
+------+----------+----------+----------+----------+

what single query i have to write for this. i tried this
query and it displays like the below

mysql> select id,sum(sales) as quarter1,(select sum(sales)
from store where mont
h>3 and month<7 ) as quarter2,(select sum(sales) from store
where month>6 and mo
nth<10)as quarter3 from store where month>0 and month<4
group by id;
+------+----------+----------+----------+
| id | quarter1 | quarter2 | quarter3 |
+------+----------+----------+----------+
| 1 | 400 | 1100 | 1640 |
| 2 | 900 | 1100 | 1640 |
+------+----------+----------+----------+
2 rows in set (0.00 sec)

tel me how to rectify it.

Question Posted / ramachchandran

2 Answers
4543 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

mysql> select * from store; +------+-------+-------+ | id | month | sales | +------+-------+..

Answer / bd

SELECT t.id,quater1,quater2,quater3,quater4 FROM store t,
(SELECT id,sum(sales) as quater1 FROM store WHERE month
IN(1,2,3) group by id) as q1 ,
(SELECT id,sum(sales) as quater2 FROM store WHERE month
IN(4,5,6) group by id) as q2 ,
(SELECT id,sum(sales) as quater3 FROM store WHERE month
IN(7,8,9) group by id) as q3 ,
(SELECT id,sum(sales) as quater4 FROM store WHERE month
IN(10,11,12) group by id) as q4
WHERE q1.id=t.id AND q2.id=t.id AND q3.id=t.id AND
q4.id=t.id GROUP BY t.id

Is This Answer Correct ?

1 Yes

0 No

mysql> select * from store; +------+-------+-------+ | id | month | sales | +------+-------+..

Answer / ramachandran

try this also my dear friend and thanks for ur answer too
select s.id,
sum(sales),
(select sum(sales)from store a where month in(1,2,3) and
a.id=s.id)as q1,
(select sum(sales)from store b where month in(4,5,6) and
b.id=s.id)as q2,
(select sum(sales)from store c where month in(7,8,9) and
c.id=s.id)as q3,
(select sum(sales)from store d where month in(10,11,12)
and d.id=s.id)as q4
from store s group by id;

Is This Answer Correct ?

1 Yes

0 No

Post New Answer