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
Peter NordbergIT ManagerAsked:
Who is Participating?
 
tigin44Connect With a Mentor 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
 
SharathConnect With a Mentor 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
 
Peter NordbergIT ManagerAuthor Commented:
Thanks for help!

Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.