CODEWITHSUNDEEP
sqlgroup-by
rajh2504
rajh2504

Reputation: 1266

SQL count query within query

I have two tables that I'm working with One dependent on the other. And I would like to get the information of the independent tables and count the number of times each row of data was used by the dependent table. With My current query I only get a record count of 1 when I know there in fact 38 records total. What am I doing wrong?

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid

Upvotes: 0

Views: 756

Answers (5)

Zulan
Zulan

Reputation: 22670

I suspect what you want to do is add a

GROUP BY b.rid

assuming your question is "How many brews are there for each recipe."

Also you might want to use a LEFT JOIN to also count 0 rows when there is no brew to a recipe:

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r LEFT JOIN brews b
ON b.rid = r.rid
WHERE r.uid = '#cookie.id#'
GROUP BY b.rid

Upvotes: 2

Andomar
Andomar

Reputation: 238086

I'm surprised the query even works without a group by? Try:

SELECT  r.rid
,       r.name
,       COUNT(b.bid) AS brewtot 
FROM    recipes r
JOIN    brews b
ON      b.rid = r.rid
WHERE   r.uid = '#cookie.id#'
GROUP BY
        r.rid
,       r.name

On SQL Server with my test setup, this returns the correct count.

Upvotes: 0

Zimbabao
Zimbabao

Reputation: 8240

You have to do group by on rid.

SELECT r.rid, r.name, COUNT(b.bid) AS brewtot 
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid 
group by r.rid

Do you want to also see all brews record too?.

Upvotes: 0

Galz
Galz

Reputation: 6832

Add 'GROUP BY r.rid' at the end of your query.

Upvotes: 0

John Sobolewski
John Sobolewski

Reputation: 4572

select r.rid, r.name, count(b.bid) as brewtot
from recipes r inner join brews b on r.rid = b.rid 
where r.uid = '#cookie.id#' group by r.rid, r.name

That should work

Upvotes: 1

Related Questions