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
- 4538 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 View All Answers
How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
What is a delimiter in mysql?
What is 'mysqlshow'?
What is a longblob?
How to get a list of all tables in a database?
What are the differences between char and nchar?
Can foreign key have duplicate values?
Which datatype is used for email in mysql?
Can you tell how to find the number of rows in a resultset using php?
List data types in mysql? Explain
What are date and time data types?
Why do gaps in sequences occur?
Is mysql owned by oracle?
How to get the number of rows selected or affected by a sql statement?
What does mysql flush tables do?
