CODEWITHSUNDEEP
sqlcommon-table-expression
Mezus
Mezus

Reputation: 35

Can you combine 3 CTE's into one?

I have a query written with 3 CTE's and I need to get the exact same result using only 1 CTE. Can someone help me?

WITH Q1(cid, sumEP) AS
(
    SELECT customerid, SUM(Extendedprice) 
    From "Invoices" 
    GROUP BY customerid 
), Q2(cid, oid, mf) AS 
(
    SELECT DISTINCT customerid, orderid, freight 
    FROM "Invoices" 
), Q3 AS 
( 
    SELECT cid, SUM(mf) AS smf 
    FROM Q2 
    GROUP BY cid 
)
SELECT Q1.cid, sumEP + smf AS total 
FROM Q1 
JOIN Q3 ON Q1.cid = Q3.cid 
LIMIT 10

Upvotes: 0

Views: 149

Answers (1)

trincot
trincot

Reputation: 350310

smf is the sum of distinct values of freight per order (for the same customer), but this you can do with sum(distinct freight)

So I would suggest this query:

WITH Q(customerid, orderid, total) AS 
(
    SELECT   customerid, orderid, SUM(Extendedprice) + SUM(DISTINCT freight)
    FROM     Invoices
    GROUP BY customerid, orderid 
)
SELECT customerid, SUM(total) AS total
FROM   Q
GROUP BY customerid
ORDER BY 2 DESC
LIMIT 10;

When there are no cases where different orders for the same customer have the same freight value, then it can be simplified to:

SELECT   customerid, SUM(Extendedprice) + SUM(DISTINCT freight) AS total
FROM     Invoices
GROUP BY customerid 
ORDER BY 2 DESC 
LIMIT 10

Note I added an order by so that you limit the results with some logic behind it -- in this case getting the top-10 by total. Change as needed.

Upvotes: 1

Related Questions