How to write a SQL query to generate a report in mysql

Question

I need to generate a report like the below table

Collection       |Model|Units Sold|Price USD |Price Euro|Price gbp
-----------------+-----+----------+----------+----------+----------
Pays de la Loire | 1301|         2|      1000|        600|      400
Toscana          | 1301|         1|       500|        300|      200
Provence         | 1302|         1|       400|        400|      400

Below are the 3 tables from where this above i need to generate

order Table

ID|collection_fk|model_fk
--+-----------+---------
 1|          1|        1
 2|          1|        1
 3|          2|        1
 4|          3|        2

Collection Table

ID|Collection
--+-----------
 1| Pays de la Loire
 2| Toscana
 3| Provence
 4|  Lorraine

Models Table

ID|model_no   |collection_fk|price_usd|price_euro|price_gbp
--+-----------+-------------+---------+----------+---------
 1|       1301|            1|     500|        300|      200
 2|       1302|            1|     400|        400|      400
 3|       1303|            2|     300|        200|      500
 4|       1304|            3|     200|        100|      300

How would i write a query to get sum of same collection and models and count number of units sold an count their price accordingly.

the first section is the result that i need from the below 3 table order Table,Collection Table,Models Table. — smrutiranjanpanda
– smrutiranjanpanda, Commented Aug 9, 2015 at 5:49
Do you have any code that you have tried? No offense, because I'm rather new here as well, but I think questions are supposed to include code that you have already tried. The way you phrased your question, just seems like you'd like it coded for you. Which I started doing, but then realized that you have no code :-) — user8735467239
– user8735467239, Commented Aug 9, 2015 at 5:56
@user3380012 are you sure the posted sum of price is correct ? i guess usd price for model 1301 should be 1000 for 2 units — M Khalid Junaid
– M Khalid Junaid, Commented Aug 9, 2015 at 5:57
@MKhalidJunaid yes sorry for my mistake you are right for moel 1301 price will be 100 usd price — smrutiranjanpanda
– smrutiranjanpanda, Commented Aug 9, 2015 at 6:03

M Khalid Junaid · Accepted Answer · 2015-08-09 06:03:28Z

2

Use joins with some aggregation

select 
c.Collection,
m.model_no,
count(o.ID) units_sold,
sum(m.price_usd),
sum(m.price_euro),
sum(m.price_gbp)
from model m
join `order` o on m.ID = o.model_fk
join collection c on c.ID = o.collection_fk
group by c.Collection,m.model_no
order by m.model_no

DEMO

answered Aug 9, 2015 at 6:03

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write a SQL query to generate a report in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related