Reputation: 5532

Please help on a complicated sql query

MySQL 5.0.45

Table A has the following fields (columns): 1. transcation_id
2. client_name
3. item_id
4. .....

Now I need to find how many transactions each client has made order by # of transactions. The result should be like:

Tom 7 transactions
Jack 5 transactions
Mike 2 transactions

If a client has no transactions his name should not be int he list.

Thank you in advance!

Upvotes: 0

Answers (3)

Reputation: 1758

Something like this?

Select client_name, count(*) As MyCount
From YourTableA
Group By client_name
Having MyCount > 0
Order by MyCount Desc

Edit: grr, too slow again! At least I got the aliases in...

Upvotes: 1

Reputation: 7731

How about:

select client_name, count(*) as transactions
from TableA
group by client_name
order by count(*) DESC

Assuming that clients without transactions aren't in the table (since the table has a transaction_id column) they won't be in the result.

Upvotes: 4

Reputation: 2988

Select 
    Client_Name,
    count(*) as Transactions
from TableA
group by Client_Name
order by count(*) desc

Upvotes: 3