Sales revenue by country

Question

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

Answer 1

Hi berkynr,

It would be great if you could provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your code attempt implementation of it in T-SQL. 
(3) Desired output based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;)

Answer 2

I can see two errors:

join motorcycle_model m on m.id=s.model.id

One dot should be an underscore.

group by 1,2

While you can say "ORDER BY 1, 2", you can use this with GROUP BY. With GROUP BBY you must use the columns or expression you want to GROUP BY. The reason for this seemingly incosistens is found in how a SELECT statement is computed *logically?. The order is:

1. FROM-JOIN
2. WHERE
3. GROUP BY 4. HAVING
5. SELECT
6. ORDER BY

Thus ORDER BY can refer expressions in the SELECT list, since is computed later, but GROUP BY cannot not since GROUJP BY happens earlier.

I stress again that this is a logical order. The physical order may be different.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

I see the question asking for all motorcycle models which probably implies we want to see all models in the final output even if there were no sales of that particular model.

So,

;with totalSales as (select c.[Name] as Country, S.model_id, sum(quantity) as Sold

from Sales INNER JOIN Country C on S.country_id = C.Id

where SalesDate >='20180101' and SalesDate < '20190101'

GROUP BY S.Country_ID, S.Model_ID, c.[Name])

select M.[Name] as Model, ISNULL(M.Price * cte.Sold,0) as [Revenue], Cte.Country

from MotorCycle_Model M 

LEFT JOIN cte on M.Model_ID = cte.Model_ID

ORDER BY cte.Country, M.[Name]

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 4

Hi berkynr，

Please try:

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 motorcyle_model m on m.id=s.model_id
where s.sales_date between '2018-01-01' and '2018-12-31'
group by c.name,m.name

Best Regards
Echo

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 5

>> I have three tables 

Really? Then why don't see any DDL for them? 

– –  When you say motorcycle ID, did you mean the VIN? Since VINs are unique, aggregating on them would make no sense. However, vehicles are identified by a make and model that is unique to the manufacturer. 

CREATE TABLE Motorcycles
(motorcycle_model VARCHAR(20) NOT NULL,
 motorcycle_make VARCHAR(20) NOT NULL,
 motorcycle_price DECIMAL(10,2) NOT NULL
 CHECK(motorcycle_price > 0.00),
PRIMARY KEY (motorcycle_model, motorcycle_make));

– – Did you know that there is an ISO standard code for countries? Did you know that the generic "id" for motor vehicles is not what it's called? In fact, there is no such thing as a generic identifier in data modeling; it has to bethe identifier of something in particular.

CREATE TABLE Countries
(iso_country_code CHAR(3) NOT NULL PRIMARY KEY,
 country_name VARCHAR(15) NOT NULL);

CREATE TABLE Sales
(motorcycle_model VARCHAR(20) NOT NULL,
 motorcycle_make VARCHAR(20) NOT NULL,
   REFERENCES 
 Motorcycles (motorcycle_model, motorcycle_make)
 iso_country_code CHAR(3) NOT NULL 
  REFERENCES Countries(iso_country_code),
 sale_qty INTEGER NOT NULL
 CHECK(sale_qty > 0), 
 sales_date DATE DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (motorcycle_model, motorcycle_make, sales_date));

This is called a relational database because there were relationships among the tables. Notice we have check constraints to ensure referential integrity. 

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.

SELECT C.iso_country_code, M.motorcycle_model, M.motorcycle_make
SUM (M.price * S.sale_qty) AS sales_revenue_tot
FROM Sales AS S,
      Countries AS C,
       Motorcycles AS M
WHERE C.iso_country_code = S.iso_country_code
    AND M.motorcycle_model = S.motorcycle_model
    AND M.motorcycle_make  = S.motorcycle_make
   AND S.sales_date BETWEEN '2018-01-01' AND  '2018-12-31'
GROUP BY C.iso_country_code, M.motorcycle_model, M.motorcycle_make; 

You never use positional numbers in a group by. It's not ANSI/ISO standard, it makes no sense if you think about it and it will get screwed up over time and the tables change even slightly

 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

Hi berkynr
Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.