CODEWITHSUNDEEP
sqlselectcount
user1355062
user1355062

Reputation:

SQL select and count at the same time

I am trying to select all from stuff and count the total amount of items in morestuff where stuff id = morestuff id.

select *,
    COUNT(morestuff.items) as total
from stuff,
    morestuff
where stuff.id = '{$id}'
    and morestuff.id = stuff.id

Obviously there is something wrong with my query, can anyone help?

Upvotes: 3

Views: 9397

Answers (2)

Steve Kass
Steve Kass

Reputation: 7184

This may be another option:

select
  *, (
    select count(*)
    from morestuff
    where morestuff.id = stuff.id
      ) as total
from stuff
where id = '{$id}'

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171569

SELECT s.*, coalesce(ms.Count, 0) as Count
FROM stuff s
left outer join (
    select id, count(*) as Count
    from morestuff
    group by id
) ms on s.id = ms.id
WHERE s.id='{$id}'

Upvotes: 3

Related Questions