CODEWITHSUNDEEP
sql
Snapula
Snapula

Reputation: 181

In SQL how to get percent of group by date?

I am trying to generate a SQL query from the following data in which results in the percentage of total sales by region for each date. There can be multiple entries for the same region on the same date that I need to sum

Date Region Sales
Jan 1, 2021 Northeast 100
Jan 1, 2021 Northeast 80
Jan 1, 2021 Midwest 75
Jan 1, 2021 West. 120
Jan 9, 2021 Northeast 110
Jan 9, 2021 Northeast 90
Jan 9, 2021 Midwest 55
Jan 9, 2021 West 80

I've tried:

SELECT "Date","Region",SUM("Sales") as "TotalUSD",
      SUM("Sales") * 100 / SUM(SUM("Sales"))  OVER () AS Percentage
FROM "sales_db"."sales_table"
GROUP BY "Date","Region"

but this is not giving the correct percentages by region by date. I think it might be giving the percentage of total by region over all dates. Any help is greatly appreciated.

Upvotes: 1

Views: 1876

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you want the proportion within a date, then date should be in the partition by:

SELECT "Date", "Region", SUM("Sales") as "TotalUSD",
       SUM("Sales") * 100.0 / SUM(SUM("Sales"))  OVER (PARTITION BY "Date") AS Percentage
FROM "sales_db"."sales_table"
GROUP BY "Date", "Region";

Note: I strongly advise you to get rid of the double quotes in the identifier names. If necessary, re-create the table. All the double quotes just make the query harder to write and to read.

Also, I added a .0. Some databases do integer division, which can also throw results off a bit.

Upvotes: 1

Srijon Chakraborty
Srijon Chakraborty

Reputation: 2154

You did not mentioned the database or its version. So, here is a code given below for SQL SERVER 2012 and MYSQL 8.0

CREATE TABLE MySales(Date DATETIME,Region Varchar(20),Sales Decimal(18,2));--Table Schema

WITH CTE AS
    (SELECT Date,Region,Sales,SUM(Sales) OVER() TotalUSD,
           SUM(Sales)  OVER (partition by Region,Date ) AS TotalSumOfGroup
    FROM MySales
    )
    SELECT *,((Sales/TotalSumOfGroup)*100) Parcentage FROM CTE
    ORDER BY Region

Note: Please check MySQL Code in DB Fiddle link with your data.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You have to use partition by clause as follows:

SELECT "Date","Region",SUM("Sales") as "TotalUSD",
      SUM("Sales") * 100 / SUM(SUM("Sales"))  OVER (partition by "Region") AS Percentage
FROM "sales_db"."sales_table"
GROUP BY "Date","Region"

Upvotes: 0

Related Questions