MySQL Query Syntax Help...

jaylee

I have the following table structure:

uid date category (not fixed, may be A, B, C, Z, or any string)
1 2001-10-31 A
2 2001-10-31 A
3 2001-10-31 B
4 2001-10-31 B
5 2001-11-01 A
6 2001-11-01 B
7 2001-11-01 C
8 2001-11-01 C

I would like to generate a query result as of the following, what query syntax should I use:

date A B C (number of occurrence)
2001-10-31 2 2 0
2001-11-01 1 1 2

Anon

www.mysql.com

look up the count function

jaylee

I know how to do individual count, but in my case I need to generate a report with combined result.

[deleted]

If you want the report to look like that, you will have to use a combo of SUM,CASE and GROUP BY

SELECT SUM(CASE category WHEN 'A' THEN 1 ELSE 0) as cat_A,
SUM(CASE category WHEN 'B' THEN 1 ELSE 0) as cat_B
FROM table
GROUP BY date;

But this requires that you first know all the possible categories.

You can also try this:
SELECT date, category, count(*) as cnt
FROM table
GROUP BY category, date;

try it out in the commandline and see what you get.

jaylee

hi vincent,

I now have the following table (accesskanguage) structure:

uid [unsigned int]
datetime [timestamp(8)]
browserlanguage [varchar(5)]

Some of the data I got in my table looks like the following:

19 20011029 en-us
20 20011029 zh-cn
21 20011029 en-us
22 20011029 de
23 20011029 en-us
24 20011030 zh-tw
25 20011030 en-us
26 20011030 zh-cn
27 20011031 en-us
28 20011031 de
29 20011031 zh-cn
20 20011031 zh-tw
30 20011031 en-us

I use the following:

SELECT datetime, language, COUNT(*) as count FROM accesslanguage GROUP BY LANGUAGE ORDER BY count DESC

The result I got only contains report of 20011029 with counts of each intividual language, but I would also like to have all the other date as well printed on the same page.

The only way I can think of right now is to use a WHERE statement to indicate a particular datetime I would like to query in addition to my above query.

Could you tell me how to show the language distribution of each individual date?

Thanks,

Jay

[deleted]

You did not get the report just for "20011029", MySQL (wrongly) printed that date because it qwas the first date it found. Normally a database would not let you do that query, because you are grouping by LANGUAGE, and there are several different values for datetime for each value of language.
You should be able to get by with just adding datetime to the GROUP BY clause:

SELECT datetime, language, COUNT(*) as cnt
FROM accesslanguage
GROUP BY LANGUAGE, DATETIME
ORDER BY cnt DESC

Notice I changed 'as count' to 'as cnt' because 'count' is a reserved word in MySQL.