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 ...

Answers


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.


Need Your Help

Porting my hibernate Dao layer in a stand-alone java app?

java spring hibernate spring-mvc

I have a spring mvc application that I am using Hibernate with.

php code after jQuery $.post() is executed twice

php ajax execution

I am still new to PHP and AJAX but I think I have some grasp about JavaScript and jQuery. Here is my problem: In my UI I have a form input field and an OK-button (which is not part of the form).