How do I count rows in MySQL?
I know just about the basic usage of COUNT(*) and I wonder if I can use it or some other function to get the following result.
I have a table with people and the products they have purchased (product_id_). I have second table which maps each product_code to a single product_category.
Using a simple SELECT I can combine both tables to get:
first last product_code product_category John BGood 100 Food John BGood 29 Beverage John BGood 30 Beverage Rita Black 25 Fashion Betty Rock 36 Electronics Betty Rock 72 Food Betty Rock 100 Food Betty Rock 36 Electronics
But what I would like is to count for each person the number of products it purchased from each category. product_category is an enum with 5 possible values (the four above and Other). I would like to get a table like:
first last product_category count John BGood Food 1 John BGood Beverage 2 John BGood Fashion 0 John BGood Electronics 0 John BGood Other 0 Betty ...
SELECT first, last, product_category, COUNT(product_code) FROM <table> ORDER BY last, first GROUP BY first, last, product_category
Try this query
SELECT first, last, product_category, count(product_category) FROM <table_name> GROUP BY product_category
Append GROUP BY person_id, product_category to your SELECT.