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
- 4629 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
Where MyISAM table will be stored and also give their formats of storage?
how to get only updated, deleted , inserted records after certain interval time in mysql with out using triggers...
How do I start mysql?
What is the innodb in mysql?
How do stored procedures work?
How to create and drop view in mysql?
What does mysqli stand for?
How to use mysqldump to create a copy of the database?
How triggers can be used in mysql?
In how many ways we can retrieve the data in the result set of MySQL using PHP? What is the difference between mysql_fetch_object and mysql_fetch_array ?
What is pragma serially_reusable and why is it used?
Why phpmyadmin is used for mysql?
How to create trigger in mysql phpmyadmin?
How many TRIGGERS are allowed in MySql table?
What is the phantom problem?
