SEO

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

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`

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

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

Good idea

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`

Thanks a million!

Regards,

Jan Nordgreen

Above is shown how to add up numbers with and without a condition
($ask_answers_condition) and give the results in one row.

Here is my attempt to do the same for max, min, and average of time
records:

SEC_TO_TIME(avg(TIME_TO_SEC(ask_answers.timetaken) )) as average,
max(ask_answers.timetaken) as max,
min(ask_answers.timetaken) as min,
SEC_TO_TIME(avg(TIME_TO_SEC(if('.$ask_answers_cond ition.',
ask_answers.timetaken, null)))) as condaverage,
min(if('.$ask_answers_condition.', ask_answers.timetaken,
"99999:59:59")) as condmin,
max(if('.$ask_answers_condition.', ask_answers.timetaken,
"00:00:00")) as condmax

It seems to work!

Regards,

Jan Nordgreen