# Possible to "sum" based on 2 different criteria? - MySQL

Often I see an OP get "chastised" for not providing enough information - I'll probably be chastised for providing "too much" information. :-) Here's a little background I have a table (computed_visits) that, amonst other columns, has columns for employee - employee number sdate - start date paidt - paid time For each day of the pay week, an employee will have from 1 to "n" rows for any given day. To get the total for an employee for a single day, I can easily sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate' To get the week ...

1. ## Possible to "sum" based on 2 different criteria?

Often I see an OP get "chastised" for not providing enough information
- I'll probably be chastised for providing "too much" information. :-)

Here's a little background

I have a table (computed_visits) that, amonst other columns, has
columns for
employee - employee number
sdate - start date
paidt - paid time

For each day of the pay week, an employee will have from 1 to "n" rows
for any given day.

To get the total for an employee for a single day, I can easily
sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate'

To get the week to date total for an employee, I can easily
sum(paidt) as paidtime where employee = 1234 and sdate >=
'some_start_date' and sdate <= 'some_end_date'

Now, my question:
Is there a query that would allow me to get "both" the employee's
total for a single day (yesterday) AND their week to date total?

What I am doing now is first getting the week to date totals and if I
get a row returned, I run another query for just the previous day (if
I don't get any week to date, the employee obviously doesn't have any
time for yesterday).
I then combine the data (in code) from both of these queries for my
report. But I'm dealing with 825+ employees, so the ability to do
this in 1 query would eliminate 825+ selects.

TIA
Lee

Lee Guest

2. ## Re: Possible to "sum" based on 2 different criteria?

On Jul 4, 3:33 pm, Lee Peedin <rr.com> wrote:

Suppose this week runs from 2007-07-02 to 2007-07-06 and that
"yesterday" is 2007-07-03. Then a query like this should work:

select sum(paidt) totalPaidt,
sum(case when sdate = '2007-07-03' then paidt else 0 end case)
yesterdayPaidt
where employee = 1234 and sdate between '2007-07-02' and '2007-07-06'

Never underestimate the power of a CASE statement inside an
aggregate :)

ZeldorBlat Guest

3. ## Re: Possible to "sum" based on 2 different criteria?

On Wed, 04 Jul 2007 20:15:15 -0000, ZeldorBlat <com>
wrote:

>
>Suppose this week runs from 2007-07-02 to 2007-07-06 and that
>"yesterday" is 2007-07-03. Then a query like this should work:
>
>select sum(paidt) totalPaidt,
> sum(case when sdate = '2007-07-03' then paidt else 0 end case)
>yesterdayPaidt
>where employee = 1234 and sdate between '2007-07-02' and '2007-07-06'
>
>Never underestimate the power of a CASE statement inside an
>aggregate :)
>[/ref]

Looks quite workable - will give it a shot when I get back to the
office on Thu. Of course the result inside the case will have to
"sum()" as well. Note my statement above "an employee will have from
1 to "n" rows for any given day."

Thanks!

Lee

Lee Guest

4. ## Re: Possible to "sum" based on 2 different criteria?

On Jul 4, 7:25 pm, Lee Peedin <rr.com> wrote: [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]

>
> Looks quite workable - will give it a shot when I get back to the
> office on Thu. Of course the result inside the case will have to
> "sum()" as well. Note my statement above "an employee will have from
> 1 to "n" rows for any given day."
>
> Thanks!
>
> Lee[/ref]

ZeldorBlat Guest

5. ## Re: Possible to "sum" based on 2 different criteria?

On Wed, 04 Jul 2007 20:42:54 -0700, ZeldorBlat <com>
wrote:

>

Yep, my fingers where ahead of my brain. I decided to VNC to the
office to run a test only to discover that our Novell server had
abended. :-(

So, it was get on the road and make the 70 mile commute to the office.
All is back up and fine now, so decided to give your suggestion a try.

Here is my actual query statement:

select coof,type,employee,sdate,hs,
sum(paidt) paidtime,
sum(paidm) paidmile,
sum(case when sdate = '20070703' then paidt else 0 end) paidty
from computed_visits
where sdate between '20070628' and '20070703'
and type = 'Normal'
and employee = '41-000100015'
group by employee

"coof" "type" "employee" "sdate" "hs" "paidtime"
"paidmile" "paidty"
"0303" "Normal" "41-000100015" "20070628" "H" "1620"
"6" "562"

Note: My dates are stored in a "non-normal" format for reasons beyond
the scope of this thread and times are in minutes.

The above statement works GREAT - thank you. The only thing I had to
change was the ending of the case statement - I had to use just "end"
vs. "end case". ???

Thanks again
Lee

Lee Guest

6. ## Re: Possible to "sum" based on 2 different criteria?

On Jul 5, 12:16 am, Lee Peedin <rr.com> wrote: [/ref]
> [/ref]
> [/ref]

>
> Yep, my fingers where ahead of my brain. I decided to VNC to the
> office to run a test only to discover that our Novell server had
> abended. :-(
>
> So, it was get on the road and make the 70 mile commute to the office.
> All is back up and fine now, so decided to give your suggestion a try.
>
> Here is my actual query statement:
>
> select coof,type,employee,sdate,hs,
> sum(paidt) paidtime,
> sum(paidm) paidmile,
> sum(case when sdate = '20070703' then paidt else 0 end) paidty
> from computed_visits
> where sdate between '20070628' and '20070703'
> and type = 'Normal'
> and employee = '41-000100015'
> group by employee
>
> "coof" "type" "employee" "sdate" "hs" "paidtime"
> "paidmile" "paidty"
> "0303" "Normal" "41-000100015" "20070628" "H" "1620"
> "6" "562"
>
> Note: My dates are stored in a "non-normal" format for reasons beyond
> the scope of this thread and times are in minutes.
>
> The above statement works GREAT - thank you. The only thing I had to
> change was the ending of the case statement - I had to use just "end"
> vs. "end case". ???
>
> Thanks again
> Lee[/ref]

Yep -- my fault. END CASE is used inside procedures and triggers; END
is used in queries.

ZeldorBlat Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•