SQL - % split between sale types - Postgresql

HenryOkwuenu
February 14, 2023
165 views
1 vote
2 Answers

I need some SQL help.

I have a table where I’m trying to get the % split between online sales and POS sales. Below is the query I have written and the screenshot is what I get, but I don’t know how to proceed from there.

I need a third column that shows the percentage split between online sales and POS sales.
That would be (POS/(POS+ONLINE))*100 and (ONLINE/(POS+ONLINE))*100

Here’s my current query

SELECT
CASE
    WHEN saleschannel = 1 THEN 'ONLINE'
    WHEN saleschannel = 6 THEN 'ONLINE'
    WHEN saleschannel = 7 THEN 'ONLINE'
    WHEN saleschannel = 8 THEN 'ONLINE'
    ELSE 'POS'
END AS CHANNEL, 
COUNT(*) AS TICKET_QTY
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
AND saleschannel IN ('1','6', '7', '8', '14')
GROUP BY CHANNEL 
ORDER BY 2;

Thanks to Aaron his query was right and helped me. I have rewritten it to make it a bit faster. This is my final code

SELECT
    channel,
    COUNT(*) AS ticket_qty,
    CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER() AS DECIMAL(10,2)) AS percentage_split
FROM (
    SELECT 
        CASE 
            WHEN saleschannel IN ('2','5','7') THEN 'ONLINE'
            WHEN saleschannel IN ('0','1','4','6','8') THEN 'POS'
            ELSE 'OTHER - (AGGREGATOR)'
        END AS channel, 
        1 AS ticket
    FROM performancesales 
    WHERE 
        salesdate BETWEEN '2022-08-01' AND '2023-01-31' 
        AND saleschannel IN ('0','1','2','3','4','5','6','7','8')
) t
GROUP BY channel;

Answers

I thnk this will do it,

WITH CTE AS 
(
SELECT
            CASE
                WHEN saleschannel = 1 THEN 'ONLINE'
                WHEN saleschannel = 6 THEN 'ONLINE'
                WHEN saleschannel = 7 THEN 'ONLINE'
                WHEN saleschannel = 8 THEN 'ONLINE'
                ELSE 'POS'
            END AS CHANNEL, 
            COUNT(*) AS TICKET_QTY
FROM 
            performancesales 
WHERE 
            salesdate BETWEEN '2022-08-01' AND '2023-01-31'
        AND 
            saleschannel IN ('1','6', '7', '8', '14')
GROUP BY 
            CHANNEL 
)

SELECT
            CHANNEL
            ,TICKET_QTY
            ,CAST(TICKET_QTY AS DECIMAL(10,2)) / SUM(TICKET_QTY) OVER() AS [Percentage]
FROM
            CTE

Wrap the original query up as a CTE (Common Table Expression) [you can call it whatever you like]. the SUM()OVER() will calculate the total sum of the dataset for each row returned. You also have to convert one of the values to a decimal otherwise you will be dividing and integer by an integer and will get an integer result. If you need more precision, do DECIMAL (10,6)

You can do this using filtered aggregation.

select 
    count(*) filter (where saleschannel in ('1','6', '7', '8')) as pos_qty,
    count(*) filter (where saleschannel = '14') as online_qty,
    count(*) filter (where saleschannel in ('1','6', '7', '8'))::numeric / count(*) * 100 as pos_percentage,
    count(*) filter (where saleschannel = '14')::numeric / count(*) * 100 as online_percentage
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
  AND saleschannel IN ('1','6', '7', '8', '14');

Please signup or login to give your own answer.

Click here to cancel reply.

SQL – % split between sale types – Postgresql

Answers