MySQL - Return number of rows matching query data?

Question

I have a query as follows:

SELECT 1 FROM shop_inventory a JOIN shop_items b ON b.id=a.iid AND b.szbid=3362169 AND b.cid=a.cid WHERE a.cid=1 GROUP BY a.bought

The only thing I need to do with this data is work out the number of rows returned (which I could do with mysqli -> num_rows;. However, I would like to know if there is a method to return the number of rows that match the query, without having to run num_rows?

For example, the query should return one row, with one result, number_of_rows.

I hope this makes sense!

Answer 1

select count(*) as `number_of_rows`
from (
    select 1
    from shop_inventory a
    join shop_items b on b.id = a.iid
        and b.szbid = 3362169
        and b.cid = a.cid
    where a.cid = 1
    group by a.bought
) a

In this case, since you are not using any aggregate functions and the GROUP BY is merely to eliminate duplicates, you could also do:

select count(distinct a.bought) as `number_of_rows`
from shop_inventory a
join shop_items b on b.id = a.iid
    and b.szbid = 3362169
    and b.cid = a.cid