I'm building a user statistics page and trying to display the statistics properly and would love some assistance.
I have a website that offers downloadable files for registered users and I would like to keep track of the downloads and how many times each user downloaded that file as well as how many unique products they downloaded from.
Table: customer_statistics
| user | product_id | file_download | date_accessed |
------------------------------------------------------------
| tom | 1104 | mp3 | 2017-05-06 00:00:00 |
| tom | 1048 | video | 2017-05-06 00:00:00 |
| tom | 1048 | video | 2017-05-06 00:00:00 |
| tom | 1048 | video | 2017-05-07 00:00:00 |
| tom | 1048 | video | 2017-05-08 00:00:00 |
| tom | 1010 | video | 2017-05-08 00:00:00 |
| tom | 1077 | video | 2017-05-08 00:00:00 |
| sue | 1749 | photo | 2017-05-09 00:00:00 |
| sue | 1284 | video | 2017-05-09 00:00:00 |
| sue | 1284 | video | 2017-05-09 00:00:00 |
| sue | 1065 | mp3 | 2017-05-09 00:00:00 |
| sue | 1344 | video | 2017-05-10 00:00:00 |
| sue | 2504 | photo | 2017-05-10 00:00:00 |
I would to display the data as such:
Name | Unique Mp3s | Unique Photos | Unique Videos | Total
----------------------------------------------------------
Tom | 1 | 0 | 3 | 7
Sue | 1 | 2 | 2 | 6
The columns unique mp3s/photos/videos show how many unique product_id each file type has been downloaded from while the total shows the total amount of all file types and occurrences added up for that specific user.
What is the best way to achieve the above result?
Thanks a bunch in advance!
I would do it this way, no subquery required:
SELECT user AS `Name`,
COUNT(DISTINCT CASE file_download WHEN 'mp3' THEN product_id END) AS `Unique Mp3s`
COUNT(DISTINCT CASE file_download WHEN 'photo' THEN product_id END) AS `Unique Photos`
COUNT(DISTINCT CASE file_download WHEN 'video' THEN product_id END) AS `Unique Videos`
COUNT(DISTINCT product_id) AS `Total`
FROM customer_statistics
GROUP BY user;