What will be the SQL query for this problem?

Question

I have a database table transaction which stores all the transaction made by users related to purchase, sell and placing order.

Table http://img691.imageshack.us/img691/1948/stackoverflow.jpg

I want to write one SQL query which gives result as following :
1. count of all transactions each person has made.
2. transactions count for every order type
3. result should be sorted from biggest total count to the lowest
4. And the result should fetch only first 10 persons info(using 'limit 0,10').

I want to fetch data which is organized already rather than fetching raw data and manipulating in PHP

So i want to write an optimized query for this problemm because this table contains thousands of rows Please guide me

thanks...

Answer 1

Try something like:

SELECT p_id, COUNT(1) AS Total, SUM(CASE WHEN order_type = 'purchased' THEN 1 ELSE 0 END) AS PurchaseCount, SUM(CASE WHEN order_type = 'placed order' THEN 1 ELSE 0 END) AS PlacedOrderCount, SUM(CASE WHEN order_type = 'sold' THEN 1 ELSE 0 END) AS SoldCount
FROM transaction
GROUP BY p_id
ORDER BY Total DESC
LIMIT 0, 10

In response to your follow up comment, I believe this would get what your after (Where the count restriction is set to a cut off for your needs)

SELECT p_id, item_name, COUNT(item_name) as ItemCount
FROM transaction
GROUP BY p_id, item_name
HAVING COUNT(item_name) > 100