COUNT and LISTING in same query

Databases
1

Is this possible to do? I thought I did it before…

I cant figure out why this will only list one category when i place the COUNT() item there.

If I change it to l.listing_id AS total it will list all 5 items though,

Am i doing something very wrong??

SELECT		c.category_id, 
			c.title,
			
		-- WHY IS THIS BELOW CAUSING ONLY 1 TO LIST???
		-- CUZ THERE ARE 5 REALLY, THEYLL WORK IF I TAKE OFF COUNT
			COUNT(l.listing_id) AS total
			
FROM		categories AS c

LEFT JOIN listings AS l
ON
(
	l.assign_category = c.category_id
)

ORDER BY	title ASC
2

So THATS what aggregate means LOL

3

so could i see the query after you added the GROUP BY clause?

i want to talk a bit more about aggregates

4

Like the gravel and stuff in concrete?

5

you’re using an aggregate function without a GROUP BY clause, that’s why

:slight_smile:

6

hahaha

7

i forgot what i wanted to say

your query looks fine

8

Heya,

Sorry I thought this thread died lol, here is my query.

SELECT		c.category_id, 
			c.title,
			c.description,
			COUNT(l.listing_id) AS total
			
FROM		categories AS c

LEFT JOIN listings AS l
ON
(
	l.assign_category = c.category_id
	AND
	l.active = 1
)


GROUP BY c.title

Im not sure for the reason of it working, but it works