CODEWITHSUNDEEP
sqlsql-server
CountMurphy
CountMurphy

Reputation: 1096

Count number of repeat rows after multi join statement

I have three tables that I need to retrieve info from: Products, ProductOptions, and OptionChoices. As of now this is the SQL I am using to get said information

select P.ProductId,P.Name,P.StoreId,PO.OptionId,OC.ChoiceName from Products P
inner join ProductOptions PO on P.ProductId=PO.ProductID
inner join OptionChoices OC on PO.OptionId=OC.OptionId
where P.ProductId=23317

which outputs this:

ProductId   Name               StoreID OptionId ChoiceName
23317   Admiral Ackbar Cereal   629     795      fish
23317   Admiral Ackbar Cereal   629     795      shrimp
23317   Admiral Ackbar Cereal   629     795      traps
23317   Admiral Ackbar Cereal   629     797      box
23317   Admiral Ackbar Cereal   629     797      casket

What would make my life a lot easier is if I could add one more column that would give me the total number of Choices for Each OptionId. So the first row would read:

ProductId   Name               StoreID OptionId ChoiceName  Count
23317   Admiral Ackbar Cereal   629     795      fish         3

Because there are 3 Choices with 795 as their OptionIds. I've tried using different combinations of group by and count, but have had no luck. Anyone have any ideas to point me in the right direction?

Edit: I am using SQL Server

Upvotes: 0

Views: 1644

Answers (4)

Dave
Dave

Reputation: 1

select
      P.ProductId
    , P.Name
    , P.StoreId
    , PO.OptionId
    , OC.ChoiceName
    , count(PO.OptionId) over (partition by P.StoreId) as OpCount
from Products P
inner join ProductOptions PO on P.ProductId = PO.ProductID
inner join OptionChoices OC  on PO.OptionId = OC.OptionId
where P.ProductId = 23317

Upvotes: 0

Thomas
Thomas

Reputation: 2445

Try this :-)

select P.ProductId,P.Name,P.StoreId, PO.OptionId, max(OC.ChoiceName), count(P.ProductId) from Products P
inner join ProductOptions PO on P.ProductId=PO.ProductID
inner join OptionChoices OC on PO.OptionId=OC.OptionId
where P.ProductId=23317
GROUP BY P.ProductId, PO.OptionId

the Problem is that you dont know what you get for field "OC.ChoiceName" - you can also use GROUP_CONCAT(OC.ChoiceName)

see: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Result for max:

ProductId   Name               StoreID OptionId ChoiceName  Count
23317   Admiral Ackbar Cereal   629     795      traps        3
23317   Admiral Ackbar Cereal   629     797      box          2

Result for GROUP_CONCAT (only MySQL!!):

ProductId   Name               StoreID OptionId ChoiceName           Count
23317   Admiral Ackbar Cereal   629     795      fish,shrimp,traps      3
23317   Admiral Ackbar Cereal   629     797      box,casket             2

Upvotes: 1

Icarus
Icarus

Reputation: 63966

You are so close...

Do: 

Select p.Productid, p.name,     
P.storeid,P.optionid, Count(choicename) 
From ( your actual query here) p
Group by p.productid,p.name,p.storeid,p.optionid

Upvotes: 1

MatBailie
MatBailie

Reputation: 86735

You need a separate query to count up the options. In this MS SQL SERVER example I've use a correlated sub-query to do the counting.

SELECT
  P.ProductId,
  P.Name,
  P.StoreId,
  PO.OptionId,
  OC.ChoiceName,
  (SELECT COUNT(*) FROM OptionChoices WHERE OptionId = OC.OptionId) AS option_count
FROM
  Products   P
INNER JOIN
  ProductOptions PO
    ON P.ProductId = PO.ProductID
INNER JOIN
  OptionChoices  OC
    ON PO.OptionId = OC.OptionId    
WHERE
  P.ProductId=23317

Upvotes: 1

Related Questions