SQL select and count at the same time

Question

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?

Aggregate functions like Count require a group by. Instead of trying to troubleshoot this via PHP, why not hit the DB directly and track down errors from there first? That might help you understand why this query doesn't work. — Marc
– Marc, Commented May 17, 2012 at 18:53
@Marc - There is no such requirement. SELECT COUNT(*) FROM my_table for example, is valid. — Chris Baker
– Chris Baker, Commented May 17, 2012 at 18:55
@Chris, point taken. I was referring to using count with other non-aggregate fields. — Marc
– Marc, Commented May 17, 2012 at 18:56
While you are executing your SQL in a PHP script it has nothing to do with PHP itself, you should try to only tag what is relevant. In this case "SQL" — thenetimp
– thenetimp, Commented May 17, 2012 at 18:57

D'Arcy Rittich · Accepted Answer · 2012-05-17 18:52:49Z

3

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}'

answered May 17, 2012 at 18:52

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

Comments

Steve Kass · Accepted Answer · 2012-05-17 19:37:34Z

2

This may be another option:

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

answered May 17, 2012 at 19:37

Steve Kass

7,22422 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL select and count at the same time

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related