[Resolved] mysql count() dillemma

perpetualshaun

I'm trying to write a simple (well I thought it would be simple at least) MySQL query. Here's the query that I thought would work:

SELECT cat_id, cat_title, cat_rank, cat_status, COUNT(cat_sub_of=cat_id) AS sub_count FROM categories WHERE cat_sub_of=0 GROUP BY cat_id ORDER BY cat_id ASC

So I want to get a result set where it lists the "top level" categories, the rank and status of each, and the count of the subcategories under each sub. For example, in the example below, there are 45 categories in my table that are a Sub Category of Top Level Cat 1 (cat_sub_of=1).

id title rank status sub_count

1 Top Level Cat 1 1 1 1
47 Top Level Cat 2 2 1 1
70 Top Level Cat 3 3 1 1

Any thoughts on how I can write a query to get all that information in one result set?

Thanks,

Shaun

paulnaj

Hi Shaun,

Are you saying that you have one table, 'categories', that contains a foreign key, 'sub_cat_of', that refers to the same table, 'categories', again?

If so, see how you get on with this ...

SELECT main.*, COUNT(sub.cat_id) AS sub_count
FROM categories as main, categories as sub
WHERE main.cat_sub_of = sub.cat_id
GROUP BY main.cat_id
ORDER BY main.cat_id ASC

... it should work!

Paul.

perpetualshaun

That gets me closer than I was Paul, but when I ran the query just like that, I got all '1's in the sub_count column. That gave me a couple ideas though, and now I'm trying to get this to work:

SELECT main.cat_id, main.cat_title, main.cat_rank, main.cat_status, COUNT(sub.cat_id) AS sub_count FROM categories as main
LEFT JOIN categories as sub ON main.cat_sub_of=sub.cat_id
WHERE main.cat_sub_of=0
GROUP BY main.cat_id
ORDER BY main.cat_id ASC

But now I have all '0's in the sub_count column. The result set looks EXACTLY what I want it to though - just need to figure out how to count the subs.

cat_id cat_title cat_rank cat_status sub_count

1 Main Cat 1 1 1 0
47 Main Cat 2 2 1 0
70 Main Cat 3 3 1 0

paulnaj

Shaun,

I don't use LEFT JOIN if I can help it :queasy: ... seems confusing to me and you can do exactly the same in the WHERE clause using AND, if necessary.

Anyway, I think you're going to have to get clear in your mind (and ours!) exactly what you want to count. 😉

Try simplifying the situation into exactly the 'nub' of the problem.

For example, suppose we have categories like this ...

Records:
+--------+-----------+------------+
| cat_id | cat_title | cat_sub_of |
+--------+-----------+------------+
|      1 | Dog       |     [NULL] |
|      2 | Bird      |     [NULL] |
|      3 | Eagle     |          2 |
|      4 | Alsation  |          1 |
|      5 | Terrier   |          1 |
|      6 | Hawk      |          2 |
|      7 | Dove      |          2 |
+--------+-----------+------------+

There are two ways to look at this ...

1) List each main category item and find out which subcategory it belongs to:

SELECT main.cat_title as main_cat_title, main.cat_id as main_cat_id
, sub.cat_title AS sub_cat_title, sub.cat_id AS sub_cat_id
FROM categories as main, categories as sub
WHERE main.cat_sub_of = sub.cat_id
ORDER BY main.cat_id ASC

Note that, in this scheme, each main cat_id has one and only one subcategory, so no point in using COUNT and GROUP BY!

Results:
+----------------+-------------+---------------+------------+
| main_cat_title | main_cat_id | sub_cat_title | sub_cat_id |
+----------------+-------------+---------------+------------+
| Eagle          |           3 | Bird          |          2 |
| Alsation       |           4 | Dog           |          1 |
| Terrier        |           5 | Dog           |          1 |
| Hawk           |           6 | Bird          |          2 |
| Dove           |           7 | Bird          |          2 |
+----------------+-------------+---------------+------------+

... or ...

2) List the subcategories, and find out which main categories they refer to ...

SELECT sub.cat_title AS sub_cat_title, sub.cat_id as sub_cat_id
, main.cat_title as main_cat_title, main.cat_id as main_cat_id
FROM categories as main, categories as sub
WHERE main.cat_sub_of = sub.cat_id
ORDER BY sub.cat_title ASC, main_cat_title ASC

Results:
+---------------+------------+----------------+-------------+
| sub_cat_title | sub_cat_id | main_cat_title | main_cat_id |
+---------------+------------+----------------+-------------+
| Bird          |          2 | Dove           |           7 |
| Bird          |          2 | Eagle          |           3 |
| Bird          |          2 | Hawk           |           6 |
| Dog           |          1 | Alsation       |           4 |
| Dog           |          1 | Terrier        |           5 |
+---------------+------------+----------------+-------------+

Now it's possible to get a count on the above, to find out how many main categories are in each subcategory ...

SELECT sub.cat_title AS sub_cat_title, sub.cat_id as sub_cat_id
, COUNT(main.cat_id) as main_cat_count
FROM categories as main, categories as sub
WHERE main.cat_sub_of = sub.cat_id
GROUP BY sub.cat_title
ORDER BY sub.cat_title ASC

... and you'd get ...

Results:
+---------------+------------+----------------+
| sub_cat_title | sub_cat_id | main_cat_count |
+---------------+------------+----------------+
| Bird          |          2 |              3 |
| Dog           |          1 |              2 |
+---------------+------------+----------------+

... as expected.

I hope that helps to clarify things. See how you get on!

Paul 🙂

perpetualshaun

Thanks for your help on this Paul! I just about have the query doing everything that I want it to. Just one more part to add... Here's the query I'm running now:

SELECT main.cat_id AS cat_id, main.cat_title AS cat_title, main.cat_rank AS cat_rank, main.cat_status AS cat_status, COUNT(sub.cat_id) as sub_cat_count
FROM categories AS sub, categories AS main
WHERE sub.cat_parent_id=main.cat_id
GROUP BY main.cat_id
ORDER BY main.cat_rank ASC

Which gives me a result set like this:

+--------+----------------+-------------------+
| cat_id |    cat_title    | sub_cat_count |
+--------+----------------+-------------------+
|      1    | Category 1  |     45                |
|      47  | Category 2  |     22                |
|      70  | Category 3  |     3                  |
|      74  | Category 4  |     8                  |
|      83  | Category 5  |     19                |
+--------+----------------+-------------------+

My original table has the fields cat_id, cat_parent_id, cat_title, and what you see above is a sample of the results where the cat_parent_id=0. This gives me EXACTLY what I'm looking for on the top level category view.

However, I also want to be able to pass the the id number of the parent one to the query and get the count under it. Does that make sense? Like in the example above, I want to be able to pass the cat_id of 1 to the query, and get a list like this of the 45 categories that are "sub categories" under Category 1 and again look for the sub_cat_count. With the data that I have, the sub_cat_count will be 0 in most cases.

Any thoughts? I've tried adding "AND sub.cat_parent_id=1" to my query but that just gives me the first result set as above, not the 45 under it.

Thanks

paulnaj

Hi Shaun,

Hmmm ... nah, I don't think you can't do it (at least until subqueries are available ... MySQL 5 ??).

In the end, you're asking to do a summary query (those involving GROUP BY) and a detail query (those NOT involving GROUP BY).

You just have to do two queries!! The one you're doing and one that doesn't use GROUP BY.

Paul.

Roger_Ramjet

Just a note for paulnaj: Subqueries are available in MySQL as from ver 4.1
http://dev.mysql.com/doc/mysql/en/Subqueries.html

ajbrown

Saun, here's your solution:

SELECT a.cat_id, a.cat_title, count( b.cat_id )  AS numsubcats 
FROM categories a LEFT  JOIN categories b ON a.cat_id = b.parent _id WHERE a.parent_id =0 GROUP  BY a.cat_id

Paul:

JOINs execute faster than relational WHERE clauses