GROUP BY and then COUNT without a subquery?

Databases
1

Is it possible to group by and then get a total count of all the rows without a subquery? I think the answer is no, but thought it was worth asking.

This is my query at the moment, which does what I want, but has a subquery:

SELECT COUNT(*) AS count
        FROM (
            SELECT 1
            FROM img_categories
            LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = 16
            GROUP BY img_categories.img_id
        ) AS tbl
2

yes

however, the query you posted doesn’t make a lot of sense – for example, you’re using GROUP BY without any aggregate functions

perhaps you could explain what you’re trying to accomplish?

3

Thanks for your reply. You made me think about why I had that GROUP BY in there, and answer was that I didn’t need it and could just as easily use DISTINCT. So now I have it with no subquery!

SELECT COUNT(DISTINCT img_id)
            FROM img_categories
            LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id =16
4

nice job

by thw way, when you have a WHERE condition that has to be not null (i.e. node.lft), then you want INNER JOIN, not LEFT OUTER JOIN

5

Thanks for the advice!

Dave