1. Categories >> Software >> Databases >> MySQL
  2. Suggest New Category

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

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

More MySQL Interview Questions

What is max_used_connections in mysql?

0 Answers  

Can you tell the difference between ereg_replace() and eregi_replace()?

0 Answers  

How to get a list of indexes of an existing table?

0 Answers  

What is difference between mongodb and mysql?

0 Answers  

What is mysql command line?

0 Answers  




Why we use mongodb instead of mysql?

0 Answers  

What is field in mysql?

0 Answers  

How do I use mysql?

0 Answers  

How large can a mysql database become?

0 Answers  

How we can see all indexes defined for a table in mysql?

0 Answers  

The table tbl_sites contains the following data: ----------------------------------------------------- Userid sitename country ------------------------------------------------------ 1 sureshbabu indian 2 PHPprogrammer andhra 3 PHP.net usa 4 PHPtalk.com germany 5 MySQL.com usa 6 sureshbabu canada 7 PHPbuddy.com pakistan 8. PHPtalk.com austria 9. PHPfreaks.com sourthafrica 10. PHPsupport.net russia 11. sureshbabu australia 12. sureshbabu nepal 13. PHPtalk.com italy Write a select query that will displayed the duplicated site name and how many times it is duplicated?

4 Answers   Afixi, GE,

what is the difference between MyIsam and InnoDb engine?

2 Answers  

For more MySQL Interview Questions Click Here
Categories