Geoff McLennan Geoff McLennan - 4 months ago 8
SQL Question

Finding a percentage per row of a grouped table

I have a table containing orders of many different products. Currently I can count the number of orders per product ID using a query like:

SELECT ProductId, COUNT(*) as NumOrders from Orders
GROUP BY ProductId;


This gives a table listing all product id's and the number of orders of that product. I can also count the number of orders that fit a certain criteria by adding a simple where clause:

SELECT ProductId, COUNT(*) as NumCriteria from Orders
WHERE HasCriteria = 1
GROUP BY ProductId;


This gives me a similar table of product id's and number of orders of that product, but smaller number due to not all orders fitting that criteria.

What I need to do is run both counts in a single query so that it displays both the number of orders that fit the criteria and the total number of orders per product ID, then also calculate the percentage of the orders that fit the criteria per product ID. Essentially something like this:

SELECT Product ID,
COUNT( *meets criteria* ),
COUNT( *total* ),
( COUNT( *meets criteria* ) * 100 / COUNT( *total* ) )


So that my output gives me something like this:

ProductID | NumCriteria | NumOrders | Percent
0001 | 5 | 10 | 50
0002 | 4 | 20 | 20
0003 | 2 | 6 | 33


So far all methods I've seen to calculate a percentage of something are for counting what percentage of rows in a table meet a criteria, but I need the percentage per each different product ID in the table. Is there any way to do this in one query?

EDIT: The raw table is orders containing and order id, product id, and customer information. The same products show up in multiple orders. A brief example would be:

Order ID | Product ID | Other info...
001 | 0002 | ...
002 | 0002 | ...
003 | 0001 | ...
004 | 0003 | ...
005 | 0001 | ...
006 | 0002 | ...
007 | 0004 | ...


So the result of the first query on this table would be:

Product ID | NumOrders
0001 | 2
0002 | 3
0003 | 1
0004 | 1

Answer

Something similar to below may get what you are looking for. When the criteria isnt met for some productID's then the row will appear null in the column.

SELECT
A.Product ID,
B.NumCriteria,
A.count(*) as total,
((B.NumCriteria*100)/A.count(*))
FROM
Orders as A
LEFT JOIN (SELECT 
        ProductID, 
        count(*) as NumCriteria
    FROM
        Orders
    WHERE
        HasCriteria = 1
    GROUP BY ProductID) as B
ON A.ProductID = B.ProductID
GROUP BY ProductID