Professional Web Applications Themes

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. #1

    Default 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. #2

    Default 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. #3

    Default 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. #4

    Default 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]

    Ummm, it already does...

    ZeldorBlat Guest

  5. #5

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

    On Wed, 04 Jul 2007 20:42:54 -0700, ZeldorBlat <com>
    wrote:
     
    >
    >Ummm, it already does...[/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


    Lee Guest

  6. #6

    Default 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

Similar Threads

  1. Replies: 2
    Last Post: August 25th, 11:09 PM
  2. Selecting lines based on "Dashed Line" settings
    By Ryan_Seely@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 4
    Last Post: May 20th, 04:12 PM
  3. Any thought on "Perl Database" based on "Tie:File"?
    By Public in forum PERL Miscellaneous
    Replies: 8
    Last Post: October 20th, 04:38 PM
  4. Form processing: change the "action=" based on pull down menu
    By Sylvie Stone in forum PHP Development
    Replies: 3
    Last Post: September 17th, 02:06 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139