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

Could not load nib in bundle on iPhone device“ SIGBART”

iphone xcode nib

I am using this code to load my view but the error is occured, Here is the code:

Send wav data to from Java to Cpp via string

java android c++ audio wav

I need to be able to send a wav files to a background process in android, which is running my audio plugin code from a different package: Samsung Audio SDK.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.