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.
- 2 Answers
- 3766 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
How many entries can mysql handle?
How do you stop an access query?
How to convert character strings to dates?
How do you restart mysql?
Can foreign key have duplicate values?
What is the full form of mysql?
Is mysql workbench a dbms?
Is mysql and sql are same?
Which is better mysql or mssql?
Is oracle mysql free?
How do you connect MySQL database with PHP?
What is the difference between the primary and unique key in mysql?
What is query log in mysql?
How do you use auto increment?
how to take mysql database backup?
