Learn how to a build a cloud-first strategyRegister Now

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

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
Asked:
peternordberg
2 Solutions
 
tigin44Commented:
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
 
SharathData 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
 
peternordbergAuthor Commented:
Thanks for help!

Peter
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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