dimanche 6 septembre 2015

Multiple select with single Group By query

I have these 3 fields in the table.

trans_date | transaction_type | client_id

What I need is a count of entries by transaction_types for each date. For example,

Date : 07/07/2015 total count : 6 transaction_type 1 count : 3 ,
transaction_type 2 count : 1, transaction_type 3 count : 2 etc....

And I need this for all the dates grouped by each date.

Here's my current query,

SELECT count(id) as total_count,(select count(id) where transaction_type=1) as type1_count, (select count(id) where transaction_type=2) as type2_count,(select count(id) where transaction_type=3) as type3_count FROM tblTransactions where client_id=1 GROUP BY date(trans_date/1000, 'unixepoch')

This returns weird numbers that doesn't match. What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire