CODEWITHSUNDEEP
mysqlsql
Smith
Smith

Reputation: 5951

select count items each group

I have two tables category and adverts, i need to select all categories and the number of adverts it has in the adverts table that has at least count greater than zero

the category table

cat_id   |  Name
----------------
1        | Toys
2        | fashion
3        | electronics

The adverts table

cat_id   |  title
 ----------------
1        | a
2        | b
2        | c
1        | d
2        | e

what i expect

cat_id  | count  | Name
-----------------------
1       |2       | a
2       |3       | b

The query i tried

Select 
c.name, c.cat_id,c.parent_id, @count:= (Select Count(av.cat_id) From adsview av Where av.cat_id = c.cat_id)
from 
category c  WHERE @count > 0

i am getting and empty result, what am i doing wrong?

Upvotes: 4

Views: 122

Answers (4)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this, mate: 

SELECT
    c.cat_id,
    COUNT(a.cat_id) AS count,
    a.title
FROM
    category c
    LEFT JOIN adverts a ON a.cat_id = c.cat_id
GROUP BY
    c.cat_id
HAVING
    count > 0;

or this: 

SELECT
    c.cat_id,
    COUNT(a.cat_id) AS count,
    a.title
FROM
    category c
    INNER JOIN adverts a ON a.cat_id = c.cat_id
GROUP BY
    c.cat_id;

Upvotes: 1

Luke
Luke

Reputation: 1724

select cat_id, count(*)
from adverts
group by cat_id;

So the mySQL query engine will grab every single row from the adverts table, it'll put them into neat piles where all rows in the pile have the same category, it'll count the number of rows in each pile, and then it'll return to you a result row for each pile with the pile's id and the number of rows.

Now lets add something: we want to also get the category's name. So we indicate that in the select clause, and add a join to the from clause. The join says "for every row in table a, consider it alongside every row in table b. if some condition holds, put this combined row into the from set". You can see that joins are actually quite slow in SQL (relatively).

select c.cat_id, count(*) as count, c.name
from adverts as a join categories as c on a.cat_id = c.cat_id
group by c.cat_id;

Note also that I've aliased the tables as a and c respectively, so as to remove the ambiguity over the column name cat_id (otherwise the mySQL query engine may get confused).

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If you want to make sure that the cat_id from category table are in adverts table you need to join as

select 
c.cat_id,
c.Name,
count(a.cat_id) as `count` 
from category c 
join adverts a on a.cat_id = c.cat_id 
group by c.cat_id ;

Upvotes: 3

Vivek Gupta
Vivek Gupta

Reputation: 1055

You have to use group by function like below

select cat_id, count(*) as count
from adverts
group by cat_id;

Upvotes: 0

Related Questions