Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 4439 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
Explain what is mysql?
What is int unsigned?
How does select query work?
How do I run a query in mysql?
What is pdo :: fetch_assoc?
In which format data is stored in mysql database?
What is cursor in mysql?
What is the current version of mysql?
What is the use of mysql_fetch_array () function in php?
How many ways we can retrieve the date in result set of mysql using php?
What is 'mysqld'?
How do I start and stop mysql server?
What is the difference between mysql and oracle?
What is the default port for mysql and how it can change?
How do you say mysql?
