Count Multiple Columns - Newbie

[dbasnett]

I have an ad database that contains two category fields (an ad could appear in two different categories). I am trying to get combined counts by category in mySql. I am trying to turn this four column result into a two column result.

SELECT c1 AS cat1, count( c1 ) AS ct, c2 AS cat2, count( c2 ) AS ct
FROM ads
GROUP BY cat1, cat2 

[FunkyDexter]

I'm struggling to understand what your after here but that sql doesn't look like it would provide anything particularly useful. Could you maybe post some sample data and the desired result?

[dbasnett]

SELECT c1 AS cat1, count( c1 ) AS ct, c2 AS cat2, count( c2 ) AS ct
FROM ads
GROUP BY cat1, cat2

Here are the results. It says that there are ads in various categories (cat1 and cat2). Notice that there are two ads with category 43. What I want is just one combined count, regardless of whether the category is in c1 or c2.

cat1 	ct 	cat2 	ct
31 	1 	43 	1
43 	1 	497 	1
44 	1 	69 	1
46 	1 	485 	1
474 	1 	20 	1

Desired results:

cat 	ct
20 	1
31 	1
43 	2 	
44 	1 	
46 	1 	
69 	1
474 	1 
485 	1
497 	1

Hope this makes more sense.

[FunkyDexter]

OK, first of all, I don't think your starting query is doing what you think it is. count(*) will return you the count of records in the group but it's not checking the value in Cat 1 or Cat 2. The Group By is doing that. You are getting the right result... but only because of co-incidence.

I believe your data actually looks like this:-
cat1 cat2
31 43
43 497
44 69
46 485
474 20

Your group by is checking Cat 1 and Cat 2 AS A PAIR! Because each pair is unique you're always seeing counts of 1. Not because each number appears once, but becasue each pair appears once.

Thats kind of an aside but is worth mentioning to help you understand what's going on. Now to how get your desired result, there's a few ways to skin this particular Cat but I'd go with creating a list of all Cats. Then sub queries to get the count of Cat1s and Cat2s in the Ads table.

Hopefully you have a table of categories in which case your list of all cats will be:-

Select CatID From Categories  (or similar).

If you don't have a table like that then you can grab it from your Ads table

Select distinct Cat1 as CatID From Ads
Union
Select distinct Cat2 as CatID From Ads

Then add sub queries to get the counts:-

Select CatID,
        (Select count(*)
        From Ads
        Where Ads.Cat1 = Categories.CatID)
                  +
       (Select count(*)
        From Ads
        Where Ads.Cat2 = Categories.CatID)
From Categories

There are probably better ways of doing this using partitions but this way is nice and platform neutral.

[dbasnett]

You were correct about not getting what I thought I was getting. So I tried this (actual query):

Select distinct c1 as cid From ads
Union
Select distinct c2  as cid From ads
Select cid,
        (Select count(*)
        From ads
        Where ads.c1 = cats.cid)
                  +
       (Select count(*)
        From ads
        Where ads.c2 = cats.cid)
From cats 

cats is the category table. The first two selects are OK. The error occurs in the next select. The error is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select cid,
(Select count(*)
From ads
Where ads.c1 = ca' at line 4

pertinent part of ads table

c1 	int(10) 	Yes  	NULL  	 
c2 	int(10) 	Yes  	NULL

cats table

cats
Field 	Type 	Null 	Default 	Comments
cid 	int(10) 	No  	  	 
pid 	int(10) 	Yes  	0  	 
name 	varchar(64) 	No  	*

c1, c2 refer to cid.

sample data:
row 1 -> c1 = 10 c2 = 20
row 2 -> c1 = 20 c2 = 30
row 3 -> c1 = 40 c2 = 10

The results should look something like this using the sample data above:
10, 2
20, 2
30, 1
40, 1

[dbasnett]

This seems to work:

SELECT ctid, count( * ) AS cts
FROM (
SELECT c1 AS ctid
FROM ads
WHERE c1 IS NOT NULL
UNION ALL
SELECT c2 AS ctid
FROM ads
WHERE c2 IS NOT NULL
) AS baseview
GROUP BY ctid