Page 1 of 1

Need help with counting DB records (pivot table)

Posted: Fri Aug 08, 2008 1:23 am
by johnc71
I am trying to run query where I could count how many times users have purchased specific items. I understand this is something similar to pivot tables. I googled Pivot Tables but most of the examples are related to calculating sum of multiple records. In my case, I am not adding anything except number of times someone purchased specific thing.

Any help is greatly appreciated!

Code: Select all

id    user      date        purchase 
1    john    2008-08-01       apple
2    mary    2008-08-02       apple 
3    mary    2008-08-03       peach 
4    david   2008-08-03       pear 
5    david   2008-08-04       apple 
6    john    2008-08-04       peach 
7    david   2008-08-05       pear 
8    mary    2008-08-05       pear 
9    john    2008-08-05       pear 
10   john    2008-08-05       apple
I am trying to get these results

Code: Select all

user    peach     apple      pear 
john       1        2          1 
mary       1        1          1 
david      1        2          0
The mysql server version is 5.0.24

Re: Need help with counting DB records (pivot table)

Posted: Fri Aug 08, 2008 3:31 am
by idevlin
Not sure exactly (without some playing around which I can't do as I don't have access to SQL here), but you could use something like:

SELECT id, DISTINCT COUNT(*) FROM tablename GROUP BY id;

Am not sure if this works, as I can't run it and check, but some combination of such things (Google it?) might point you in the right direction.