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