locked
Sales revenue by country RRS feed

  • Pytanie

  • 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

    niedziela, 16 sierpnia 2020 18:41

Wszystkie odpowiedzi

  • 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;)

    niedziela, 16 sierpnia 2020 19:52

  • 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, esquel@sommarskog.se

    • Zaproponowany jako odpowiedź przez Naomi N niedziela, 16 sierpnia 2020 20:28
    niedziela, 16 sierpnia 2020 20:12
  • 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

    niedziela, 16 sierpnia 2020 20:27
  • 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.




    • Zmodyfikowany przez Echo Liuz poniedziałek, 17 sierpnia 2020 02:48
    poniedziałek, 17 sierpnia 2020 02:39
  • >> 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

    poniedziałek, 17 sierpnia 2020 19:38
  • 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.

    • Zaproponowany jako odpowiedź przez Echo Liuz czwartek, 20 sierpnia 2020 05:22
    wtorek, 18 sierpnia 2020 05:58