Need help with counting DB records (pivot table)
Posted: Fri Aug 08, 2008 1:23 am
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!
I am trying to get these results
The mysql server version is 5.0.24
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 appleCode: Select all
user peach apple pear
john 1 2 1
mary 1 1 1
david 1 2 0