ZeldorBlat wrote:
> On Aug 18, 6:59 am, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> damezumari wrote:
>>> How do I get the total without a condition and the total with the
>>> condition at the same time?
>>
>>> This is my table:
>>
>>> id datetimeconfirmed customerid amount
>>> 1 2007-08-14 06:19:03 1 12.25
>>> 2 2007-08-06 06:19:19 1 15
>>> 3 2007-08-02 06:19:41 2 5
>>> 4 2007-08-07 06:19:58 2 7.25
>>> 5 2007-08-09 06:36:41 3 25
>>
>>> If I run this query:
>>
>>> select customerid, sum(amount) from test
>>> group by customerid
>>
>>> I get this result:
>>
>>> customerid sum( amount )
>>> 1 27.25
>>> 2 12.25
>>> 3 25
>>
>>> If I run this query:
>>
>>> select customerid, sum(amount) from test
>>> where datetimeconfirmed < '2007-08-08'
>>> group by customerid
>>
>>> I get this result:
>>
>>> customerid sum( amount )
>>> 1 15
>>> 2 12.25
>>
>>> Which query do I have to run to get this result?
>>
>>> customerid sum( amount ) total
>>> 1 15 27.25
>>> 2 12.25 12.25
>>> 3 NULL 25
>>
>>> Regards,
>>
>>> Jan Nordgreen
>>
>> SELECT
>> `b`.*,
>> SUM(`a`.`amount`) `total`
>> FROM `test` `a`
>> LEFT JOIN (
>> SELECT
>> `customerid`,
>> SUM(`amount`)
>> FROM `test`
>> WHERE `datetimeconfirmed` < '2007-08-08'
>> GROUP BY `customerid`
>> ) `b` USING(`customerid`)
>> GROUP BY `a`.`customerid`
>
> You could also do something like this:
>
> select customer_id,
> sum(amount) allTotal,
> sum(case when dateconfirmed < '2007-08-08' then amount else 0
> end) condTotal
> from some_table
> group by customer_id

Come to think of it, maybe this could be made slightly clearer with an IF()
call

SELECT
`customer_id`,
SUM(`amount`) `allTotal`,
SUM(IF(`dateconfirmed` < '2007-08-08',`amount`,0)) `condTotal`
FROM `test`
GROUP BY `customer_id`