SQL Query with Count

Question

I have the following tables:

list

id - key
name

item

id - key
name

list_item

list_id - foreign key to list table
item_id - foreign key to item table

I have the following query:

SELECT l.id, l.name
FROM list l, list_item li
WHERE l.id = li.list_id

I want to add to the result set, the count of items in the list. How do I do this?

have you tried adding COUNT() to your query? Also please add some sample data and the expected result. — Taryn
– Taryn, Commented Oct 4, 2012 at 2:27

xdazz · Accepted Answer · 2012-10-04 02:28:03Z

4

SELECT l.id, l.name, COUNT(li.item_id) AS item_count 
FROM list l
LEFT JOIN list_item ON l.id = li.list_id
GROUP BY l.id

answered Oct 4, 2012 at 2:28

xdazz

161k38 gold badges255 silver badges278 bronze badges

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

4 Comments

Vikdor Over a year ago

Shouldn't l.name be part of the GROUP BY clause?

xdazz Over a year ago

l.id is the key of the list, why should l.name be part of the GROUP BY clause?

Vikdor Over a year ago

Seems to be valid in MySQL (but SQL Server complains that all columns other than those on which aggregate functions are applied, must be in the GROUP BY list). Sorry for false alarm!

xdazz Over a year ago

@Vikdor Yes, you are right, this is MySQL special thing. Your alarm make sense if this is not MySQL :)

sashkello · Accepted Answer · 2012-10-04 02:28:02Z

1

Try this:

SUM(l.id = li.list_id)

answered Oct 4, 2012 at 2:28

sashkello

18k25 gold badges84 silver badges112 bronze badges

2 Comments

Vikdor Over a year ago

Did you mean to say COUNT instead of SUM?

sashkello Over a year ago

I think SUM will work just fine here, COUNT should work as well but SUM is much more flexible...

Collectives™ on Stack Overflow

SQL Query with Count

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related