I have a table like this:
------------------------------------------------------
ID | Date | ClientName | TransactionAmount |
------------------------------------------------------
1 | 6/16/13 | C1 | 15 |
------------------------------------------------------
2 | 6/16/13 | C1 | 10 |
------------------------------------------------------
3 | 6/16/13 | C2 | 10 |
------------------------------------------------------
4 | 6/17/13 | C2 | 20 |
------------------------------------------------------
And I would like to get something like this:
------------------------------------------------------------------------
Date | C1_Total_Amount_Transacted | C2_Total_Amount_Transacted |
------------------------------------------------------------------------
6/16/13 | 25 | 10 |
------------------------------------------------------------------------
6/17/13 | 0 | 20 |
In the second table Date is unique also I there are x clients in the databse the resul table will have x + 1 columns (1 fore date and x one for each client).
There might be necessary to write some PHP code and more querys, any working solution is perfect, I don`t need a full SQL solution.
Thanks
I presume that you are rather new to SQL. This type of query requires conditional summation. And it is quite easy to express in SQL:
select `date`,
sum(case when Client_Name = 'C1' then TransactionAmount else 0 end) as C1,
sum(case when Client_Name = 'C2' then TransactionAmount else 0 end) as C2
from t
group by `date`
But, you have to list each client in the query. You always have to specify the exact column headers for a SQL query. If you don't know them, then you need to create the SQL as a string and then execute it separately. This is a rather cumbersome process.
You can often get around that by using group_concat(). This puts the values in a single column, with a separator of your choice (default is a comma):
select `date`, group_concat(amount)
from (select `date`, ClientName, sum(TransactionAmount) as amount
from t
group by `date`, ClientName
) t
group by `date`