Count number of repeat rows after multi join statement

Question

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

What version of SQL are you using?

MatBailie
– MatBailie

2011-10-05 23:04:08 +00:00
Commented Oct 5, 2011 at 23:04 — MatBailie
– MatBailie, Commented Oct 5, 2011 at 23:04
SQL server. Just updated my question

CountMurphy
– CountMurphy

2011-10-05 23:06:19 +00:00
Commented Oct 5, 2011 at 23:06 — CountMurphy
– CountMurphy, Commented Oct 5, 2011 at 23:06

MatBailie · Accepted Answer · 2011-10-05 23:07:10Z

1

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

answered Oct 5, 2011 at 23:07

MatBailie

87.6k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CountMurphy Over a year ago

That totally worked. Thanks! I am going to spend some time reading this query to figure it out.

MatBailie Over a year ago

The performance 'problems' will entirely depend on indexing and the size of the data set in question. As this query has P.ProductId=23317, the correlated sub query will only really be relevant to a small number of records. Also, note that SQL Server is pretty good at optimising this. That said, joining on (SELECT OptionID, COUNT(*) FROM OptionChoices GROUP BY OptionID) will be faster for larger data sets.

CountMurphy Over a year ago

This isn't for anything production related. I need to generate some reports and this just helps me get the task done faster.

Icarus · Accepted Answer · 2011-10-05 23:08:19Z

1

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

answered Oct 5, 2011 at 23:08

Icarus

64k14 gold badges102 silver badges116 bronze badges

5 Comments

MatBailie Over a year ago

That drops the option_name from the result set. The OP wants each record, together with the count of records in the group...

CountMurphy Over a year ago

Column 'OptionChoices.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Icarus Over a year ago

Dear SO'ers, I wrote my statement from a cell and it's kind of difficult to see the whole thing in the mobile version, if option_name is wanted, then should be selected; if repeated values are not desired, then it shoul be grouped by that column. The idea is to select all columns that Repeat themselves and do count of the ones that don't repeat, grouping by all the ones that are not enclosed in a count.

MatBailie Over a year ago

The OP wants each item in the set, without GROUPing, de-duplication, or any other functionality. Then, as an additional field, the count of the sub-group is needed. This can't be achieved with just one reference to the OptionChoices table.

Icarus Over a year ago

@dems my mistake then, I thought he only wanted distinct columns + a count of choices for same product.

Thomas · Accepted Answer · 2011-10-05 23:14:03Z

1

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

edited Oct 5, 2011 at 23:14

answered Oct 5, 2011 at 22:58

Thomas

2,4651 gold badge19 silver badges19 bronze badges

2 Comments

CountMurphy Over a year ago

Bummer, getting errors for each. For the first: Column 'Products.ProductId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. For the Second: Incorrect syntax near 'GROUP_CONCAT'. Using Sql server if that helps

Thomas Over a year ago

GROUP_CONCAT is MySQL - Sorry - forgot "P." and "PO." in "GROUP BY" - try again

Dave · Accepted Answer · 2014-09-23 19:01:02Z

0

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

answered Sep 23, 2014 at 19:01

Dave

1

Collectives™ on Stack Overflow

Count number of repeat rows after multi join statement

4 Answers 4

3 Comments

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related