Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 215

# count and divide query

Hi,
I have a query looking like this:
SELECT        dbo.shopCart.product, COUNT(dbo.shopCart.productID) AS Antal
FROM            dbo.[Order] INNER JOIN
dbo.shopCart ON dbo.[Order].orderID = dbo.shopCart.orderID INNER JOIN
dbo.[Order] AS o ON dbo.[Order].orderID = o.orderID
WHERE        (dbo.[Order].customerID IS NOT NULL)
GROUP BY dbo.shopCart.product, dbo.shopCart.productID
ORDER BY antal DESC
--------------------------------------------------
That conts how many products that has been sold per productID. I would like to add a field that counts the percentage rate of the row productIID as compared to the whole sale (all productIDs).

I would like my result to look like this:
Product               Antal           %
A product           1000           10%             (the total sale is 10 0000 products)

Is there a way to achieve this?

Thanks for help!

Peter
0
peternordberg
2 Solutions

Commented:
SELECT        dbo.shopCart.product, COUNT(dbo.shopCart.productID) AS Antal,
ROUND(COUNT(dbo.shopCart.productID) / (select COUNT(dbo.shopCart.productID) from dbo.shopCart) * 100), 0) Percentage
FROM            dbo.[Order] INNER JOIN
dbo.shopCart ON dbo.[Order].orderID = dbo.shopCart.orderID INNER JOIN
dbo.[Order] AS o ON dbo.[Order].orderID = o.orderID
WHERE        (dbo.[Order].customerID IS NOT NULL)
GROUP BY dbo.shopCart.product, dbo.shopCart.productID
ORDER BY antal DESC
0

Data EngineerCommented:
If you dont get correct percentage with Tigin's solution, then convert the counts into money.
SELECT        dbo.shopCart.product, COUNT(dbo.shopCart.productID) AS Antal,
ROUND(CAST(COUNT(dbo.shopCart.productID) AS MONEY) / CAST((select COUNT(dbo.shopCart.productID) from dbo.shopCart) AS MONEY) * 100,0) AS Percentage
FROM            dbo.[Order] INNER JOIN
dbo.shopCart ON dbo.[Order].orderID = dbo.shopCart.orderID INNER JOIN
dbo.[Order] AS o ON dbo.[Order].orderID = o.orderID
WHERE        (dbo.[Order].customerID IS NOT NULL)
GROUP BY dbo.shopCart.product, dbo.shopCart.productID
ORDER BY antal DESC

0

Author Commented:
Thanks for help!

Peter
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.