Professional Web Applications Themes

GROUP BY challenge... - Microsoft SQL / MS SQL Server

I am trying to group by a range of values. For example. OrdersTable: {ClientID,DayOfWeek,Amount} 1,MONDAY,10 1,FRIDAY,12 1,SATURDAY,18 2,MONDAY,10 QUERY: SELECT SUM(Amount) FROM OrdersTable GROUP BY ClientID,DayOfWeek The problem is, I would like to combine Monday through Friday in the same Grouping, and then have Saturday and Sunday left as seperate items. I would like to return these values. 1,Monday_to_Friday,22 1,Saturday,18 2,Monday_to_Friday,10 Thanks for any help....

  1. #1

    Default GROUP BY challenge...

    I am trying to group by a range of values. For example.

    OrdersTable: {ClientID,DayOfWeek,Amount}
    1,MONDAY,10
    1,FRIDAY,12
    1,SATURDAY,18
    2,MONDAY,10

    QUERY:
    SELECT SUM(Amount) FROM OrdersTable GROUP BY ClientID,DayOfWeek

    The problem is, I would like to combine Monday through Friday in the same
    Grouping, and then have Saturday and Sunday left as seperate items.

    I would like to return these values.
    1,Monday_to_Friday,22
    1,Saturday,18
    2,Monday_to_Friday,10

    Thanks for any help.


    rooster575 Guest

  2. #2

    Default Re: GROUP BY challenge...

    Do:

    SELECT ClientId, DayOfWeek, SUM(Amount)
    FROM (
    SELECT ClientId, Amount,
    CASE WHEN DayOfWeek IN ('Saturday', 'Sunday')
    THEN DayOfWeek
    ELSE 'Monday_to_Friday'
    END
    FROM Orders ) D ( ClientId, Amount,DayOfWeek )
    GROUP BY ClientId, DayOfWeek
    ORDER BY ClientId ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: GROUP BY challenge...

    Thanks!

    Is it possible to do this with just the 1 SELECT, or do I have to use this
    nested method?

    [I would have just tried it, but I have a complicated query I dont want to
    mess with until I'm sure what Im doing]

    example:
    SELECT ClientId, Amount,
    (CASE WHEN DayOfWeek IN ('Saturday', 'Sunday')
    THEN DayOfWeek
    ELSE 'Monday_to_Friday'
    END) As DayOfWeek
    FROM Orders
    GROUP BY ClientId, DayOfWeek



    "Anith Sen" <com> wrote in message
    news:OQqC$M%phx.gbl... 


    rooster575 Guest

  4. #4

    Default Re: GROUP BY challenge...

    >> I am trying to group by a range of values. <<

    Build a table with the ranges, join that table to your other table then
    do the groupings. If you have a continous value use this:

    CREATE TABLE Ranges
    (group_name CHAR(10) NOT NULL PRIMARY KEY,
    start INTEGER NOT NULL,
    finish INTEGER NOT NULL,
    CHECK (start < finish));

    SELECT R1.group_name, SUM(foobar)
    FROM Ranges AS R1, SomeTable AS T1
    WHERE T1.value BETWEEN R1.start AND R1.finish;

    If you have discrete values use this:

    CREATE TABLE Ranges
    (group_name CHAR(10) NOT NULL PRIMARY KEY,
    member CHAR(10) NOT NULL);

    SELECT R1.group_name, COUNT(*)
    FROM Ranges AS R1, SomeTable AS T1
    WHERE T1.value = member;


    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: GROUP BY challenge...

    Works well! Thanks to you both..


    "Bill Mittenzwey" <com> wrote in message
    news:elG1ro%phx.gbl... [/ref]
    same 
    >
    >[/ref]


    rooster575 Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Challenge #34
    By Grant in forum Adobe Photoshop Elements
    Replies: 89
    Last Post: October 4th, 08:18 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