2

I have the result of a subquery:

SELECT 
    maker, p.type, COUNT(DISTINCT pc.model) pcs
FROM 
    Product p 
INNER JOIN 
    Pc pc ON p.model = pc.model
GROUP BY 
    maker, p.type

UNION

SELECT 
    maker, p.type, COUNT(DISTINCT l.model) pcs
FROM
    Product p 
INNER JOIN 
    Laptop l ON p.model = l.model
GROUP BY 
    maker, p.type

UNION

SELECT 
    maker, p.type, COUNT(DISTINCT pr.model) pcs
FROM 
    Product p 
INNER JOIN 
    Printer pr ON p.model = pr.model
GROUP BY 
    maker, p.type
maker type model
A Laptop 2
A PC 2
A Printer 3
B Laptop 1
B PC 1
C Laptop 1
D Printer 2
E PC 1
E Printer 1

But I need to find the percentage of the number of models of a given type and a given maker to the total number of models from that maker. In other words: divide these values ​​by the total quantity by maker, i.e. SUM(COUNT(pcs))

I should get a table like this:

maker type model
A Laptop 28.57
A PC 28.57
A Printer 42.86
B Laptop 50.00
B PC 50.00
C Laptop 100.00
D Printer 100.00
E PC 50.00
E Printer 50.00
3
  • Window function count - with your existing query in a CTE/sub-query Commented May 25 at 18:46
  • 3
    Please also consider accepting answers to your previous questions. Commented May 25 at 19:28
  • Why do you put the laptop products in a separate table? Commented May 26 at 9:56

3 Answers 3

3

As proposed by @Dale K in a comment, you should use window functions that can associate to each row of your result set, some values computed on other rows of the result set sharing some common properties.

Here you want each row to compute its percentage compared to other rows with the same maker (so maker is your common property to "group", or, in window terms, "partition" rows of the resultset).

So you will get your result by making your query a Common Table Expression,
which will make it appear like a table on its own, with rows from you 3 UNIONed SELECTs appear as rows of the same "virtual table"
then computing your percentages with a window function:

WITH everything AS
(
    -- This is your query, unmodified:
    SELECT maker, p.type, COUNT(DISTINCT pc.model) pcs
    FROM Product p INNER JOIN Pc pc
    ON p.model = pc.model
    GROUP BY maker, p.type
     UNION
    SELECT maker, p.type, COUNT(DISTINCT l.model) pcs
    FROM Product p INNER JOIN Laptop l
    ON p.model = l.model
    GROUP BY maker, p.type
     UNION
    SELECT maker, p.type, COUNT(DISTINCT pr.model) pcs
    FROM Product p INNER JOIN Printer pr
    ON p.model = pr.model
    GROUP BY maker, p.type
)
SELECT
  maker, type,
  ROUND(100.0 * pcs / SUM(pcs) OVER (PARTITION BY maker), 2) model
FROM everything;

(see it, and experiment on it, in this fiddle)

N.B.: Common Table Expressions is a great way to sort rows from a UNION, WITH cte AS (SELECT … UNION ALL SELECT …) SELECT * FROM cte ORDER BY x,
an alternative to sub-SELECTs (SELECT * FROM (SELECT … UNION ALL SELECT …) ORDER BY x) but improving readability and flexibility.

Sign up to request clarification or add additional context in comments.

Comments

2

Don't you want much smaller summary table?

I understand, that this is not what was asked, but maybe someone should use this result.

WITH everything AS(
  SELECT maker, 
    COUNT(DISTINCT pc.model) pc, 
    COUNT(DISTINCT l.model) l, 
    COUNT(DISTINCT pr.model) pr
  FROM Product p 
  LEFT JOIN Pc pc ON p.model = pc.model
  LEFT JOIN Laptop l ON p.model = l.model
  LEFT JOIN Printer pr ON p.model = pr.model
  GROUP BY maker
)
SELECT maker, pc/(pc+l+pr) pc, l/(pc+l+pr) l, pr/(pc+l+pr) pr
WHERE (pc+l+pr)>0

Another optimized version without unnecessary groupping:

WITH everything AS(
  SELECT maker, 
    (SELECT COUNT(*) FROM Pc pc WHERE p.model = pc.model) pc, 
    (SELECT COUNT(*) FROM Laptop l WHERE p.model = l.model) l, 
    (SELECT COUNT(*) FROM Printer pr WHERE p.model = pr.model) pr
  FROM Product p 
)
SELECT maker, pc/(pc+l+pr) pc, l/(pc+l+pr) l, pr/(pc+l+pr) pr
WHERE (pc+l+pr)>0

2 Comments

If one product associates to 2 PCs and 2 Printers and 2 Laptops, then the aggregation has to deal with 8 rows. Although the DISTINCT keyword does manage that, the duplication caused by the joins will hit CPU and Memory resources unnecessarily. I would not recommend this approach.
You are absolutely right! I'll add another variant with in-place subqueries.
2

Put your original query inside a CTE. The second CTE takes the result from your original query and performs the summary calculation. The actual query uses both CTE's to show the desired output.

WITH counts as (
  SELECT maker, p.type, COUNT(DISTINCT pc.model) pcs
  FROM Product p 
    INNER JOIN Pc pc ON p.model = pc.model
  GROUP BY maker, p.type
  UNION
  SELECT maker, p.type, COUNT(DISTINCT l.model) pcs
  FROM Product p 
    INNER JOIN Laptop l ON p.model = l.model
  GROUP BY maker, p.type
  UNION
  SELECT maker, p.type, COUNT(DISTINCT pr.model) pcs
  FROM Product p 
    INNER JOIN Printer pr ON p.model = pr.model
  GROUP BY maker, p.type
),
summary AS (
  SELECT maker, SUM(pcs) AS total
  FROM counts
  GROUP BY maker
)
SELECT 
  c.maker,
  c.type,
  CAST(c.pcs*100.0/s.total AS DECIMAL(5,2)) AS model
FROM counts c
  JOIN summary s ON s.maker = c.maker
ORDER BY c.maker, c.type;

1 Comment

Please provide an explanation to complete your answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.