Finding Sales revenue by country

Transact-SQL
1

I have three tables

  • motorcyle_model: id, name, price
  • country: id, name
  • Sales: model_id, country_id, quantity, sales_date

All the country wise sale records of its motorcycles in table sales, storing quantity sold on particular date

I need to write a query calculates country-wise sales for all of the motorcycle models along with the revenue generated for the year 2018 the order of output does not matter.

The result format as follow;

country_name, motorcyle_model, revenue

This is my answer. I got an error(your output is not correct)

select c.name, m.name, sum(m.price*s.quantity) as revenue
from sales s
join country c on c.id=s.country_id
join motorcycle_model m on m.id=s.model.id
where s.sales_date between '2018-01-01' and '2018-12-31'
group by 1,2

any help much appreciated

2

Could it be because group by 1,2 needs to change to group by c.name, m.name

3

what is this?

s.model.id
4

hi

could you please explain a little bit more about your error !!

also some sample data .. like DDL and DML statements would be nice ..

Example = Create Table

Looking at your query .. looks fine .. have to dig deeper ..based on your input