Results 1 to 6 of 6

Thread: Grouping query

  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: Grouping query

    This 3 querys:
    Select 'ComputerName', COUNT(*) as To1H
    FROM __Deprate as To1h
    where differensMIN between 0 and 60
    group by To1H.DisplayName
    order by To1H.DisplayName
    ---
    Select 'ComputerName', COUNT(*) as To4H
    FROM __Deprate as To4H
    where differensMIN between 0 and 240
    group by To4H.DisplayName
    order by To4H.DisplayName
    ---
    Select 'ComputerName', COUNT(*) as allH
    FROM __Deprate as allH
    group by allH.DisplayName
    order by allH.DisplayName
    -----------------------------------------------------------
    Looks like this:
    DisplayName To1H
    ComputerName 159
    ComputerName 246
    ComputerName 44
    ---
    DisplayName To4H
    ComputerName 162
    ComputerName 249
    ComputerName 46
    ComputerName 160
    ComputerName 59
    ---
    DisplayName allH
    ComputerName 164
    ComputerName 256
    ComputerName 104
    ComputerName 161
    ComputerName 67
    ComputerName 12
    ComputerName 603
    ComputerName 1397
    -----------------------------------------------------------
    How can i make the output to look like this?

    DisplayName To1H DisplayName To4H DisplayName allH
    ComputerName 159 ComputerName 162 ComputerName 164
    ComputerName 246 ComputerName 249 ComputerName 256
    ComputerName 44 ComputerName 46 ComputerName 104
    NULL ComputerName 160 ComputerName 161
    NULL ComputerName 59 ComputerName 67
    NULL NULL ComputerName 12
    NULL NULL ComputerName 603
    NULL NULL ComputerName 1397


    Thanks for all help/ideas in advance

    //M

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mrpcguy, My guess is you can join your 3 queries with Left (or Full) outer join and get the results you want. But the examples you gave make no sense, so I can't be sure of that.

    You have 'computer name' hard coded on all the rows. If this is supposed to represent distinct computer name (as DisplayName) you could do this (slightly modified since I don't have you data to summarize):
    Code:
    WITH TAB_T01H (DISPLAYNAME, CNT)
      AS (
          SELECT 'A', 159 UNION ALL
          SELECT 'B', 246 UNION ALL
          SELECT 'C',  44 
         )
       , TAB_T04H (DISPLAYNAME, CNT)
      AS (
          SELECT 'A', 162 UNION ALL
          SELECT 'B', 249 UNION ALL
          SELECT 'C',  46 UNION ALL
          SELECT 'D', 160 UNION ALL
          SELECT 'E',  59
         )
       , TAB_ALLH (DISPLAYNAME, CNT)
      AS (
          SELECT 'A',  164 UNION ALL
          SELECT 'B',  256 UNION ALL
          SELECT 'C',  104 UNION ALL
          SELECT 'D',  161 UNION ALL
          SELECT 'E',   67 UNION ALL
          SELECT 'F',   12 UNION ALL
          SELECT 'G',  603 UNION ALL
          SELECT 'H', 1397
         )
    SELECT T1.DISPLAYNAME, T1.CNT, T4.DISPLAYNAME, T4.CNT, TA.DISPLAYNAME, TA.CNT
    FROM TAB_ALLH TA
           LEFT OUTER JOIN
           TAB_T04H T4
             ON TA.DISPLAYNAME = T4.DISPLAYNAME
             LEFT OUTER JOIN
               TAB_T01H T1
                 ON T4.DISPLAYNAME = T1.DISPLAYNAME
    
    ORDER BY TA.DISPLAYNAME
    
    DISPLAYNAME CNT         DISPLAYNAME CNT         DISPLAYNAME CNT        
    ----------- ----------- ----------- ----------- ----------- -----------
    A                   159 A                   162 A                   164
    B                   246 B                   249 B                   256
    C                    44 C                    46 C                   104
    -                     - D                   160 D                   161
    -                     - E                    59 E                    67
    -                     - -                     - F                    12
    -                     - -                     - G                   603
    -                     - -                     - H                  1397
    
      8 record(s) selected.
    Last edited by Stealth_DBA; 02-12-11 at 15:47.

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    thx, yes the "computer Name" should represent real computer name, i just don't want to expose them.

    The querys looks like this
    Select DisplayName, COUNT(*) as To1H
    FROM __Deprate as To1h
    where differensMIN between 0 and 60
    group by To1H.DisplayName
    order by To1H.DisplayName
    ---
    Select DisplayName, COUNT(*) as To4H
    FROM __Deprate as To4H
    where differensMIN between 0 and 240
    group by To4H.DisplayName
    order by To4H.DisplayName
    ---
    Select DisplayName, COUNT(*) as allH
    FROM __Deprate as allH
    group by allH.DisplayName
    order by allH.DisplayName
    -----------------------------------------------------------
    Looks like this:
    DisplayName To1H
    Computer01 159
    Computer02 246
    Computer03 44
    ---
    DisplayName To4H
    Computer01 162
    Computer02 249
    Computer03 46
    Computer04 160
    Computer05 59
    ---
    DisplayName allH
    Computer01 164
    Computer02 256
    Computer03 104
    Computer04 161
    Computer05 67
    Computer06 12
    Computer07 603
    Computer08 1397

    and i want the result as you describe it. I have tried but i just can't get the output the way i want.

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    my bad i got it to work...

    i ues this of course:
    WITH TAB_T01H (DISPLAYNAME, To1H)
    AS (
    Select To1H.DisplayName, COUNT(*)
    FROM __Deprate as To1h
    where differensMIN between 0 and 60
    group by To1H.DisplayName
    )
    , TAB_T04H (DISPLAYNAME, To4H)
    AS (
    Select To4H.DisplayName, COUNT(*)
    FROM __Deprate as To4H
    where differensMIN between 0 and 240
    group by To4H.DisplayName
    )
    , TAB_ALLH (DISPLAYNAME, ALLH)
    AS (
    Select DisplayName, COUNT(*)
    FROM __Deprate as allH
    group by allH.DisplayName
    )
    SELECT T1.DISPLAYNAME, T1.To1H, T4.DISPLAYNAME, T4.To4H, TA.DISPLAYNAME, TA.ALLH
    FROM TAB_ALLH TA
    LEFT OUTER JOIN
    TAB_T04H T4
    ON TA.DISPLAYNAME = T4.DISPLAYNAME
    LEFT OUTER JOIN
    TAB_T01H T1
    ON T4.DISPLAYNAME = T1.DISPLAYNAME
    ORDER BY TA.DISPLAYNAME

    thank you very much for your help

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    (our posts crossed each other. you can ignore this.)

    mrpcguy, Not sure what to tell you. If the ALLH has all computer names, T04H has a subset of the names in ALLH and T01H has a subset of name in T04H, you should be able to put your queries (without the ORDER BY) in the Common Table Expression (CTE), replacing my generated data and get the results you want. If they are not subsets, you might have to joint All to 4H and ALL to 1H.
    Code:
    WITH TAB_T01H (DISPLAYNAME, CNT)
      AS (
          Select DisplayName, COUNT(*) as To1H
          FROM __Deprate as To1h
          where differensMIN between 0 and 60
          group by To1H.DisplayName
         )
       , TAB_T04H (DISPLAYNAME, CNT)
      AS (
          Select DisplayName, COUNT(*) as To4H
          FROM __Deprate as To4H
          where differensMIN between 0 and 240
          group by To4H.DisplayName
         )
       , TAB_ALLH (DISPLAYNAME, CNT)
      AS (
          Select DisplayName, COUNT(*) as allH
          FROM __Deprate as allH
          group by allH.DisplayName
         )
    SELECT T1.DISPLAYNAME, T1.CNT, T4.DISPLAYNAME, T4.CNT, TA.DISPLAYNAME, TA.CNT
    FROM TAB_ALLH TA
           LEFT OUTER JOIN
           TAB_T04H T4
             ON TA.DISPLAYNAME = T4.DISPLAYNAME
             LEFT OUTER JOIN
               TAB_T01H T1
                 ON T4.DISPLAYNAME = T1.DISPLAYNAME
    
    ORDER BY TA.DISPLAYNAME
    PS you can change the CTE derived table names from TAB_T01H, TAB_T04H, and TAB_ALLH to whatever names you like.
    Last edited by Stealth_DBA; 02-14-11 at 10:44.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    seems to me it would perform better and be easier to understand with something like:

    Code:
    Select DisplayName
         , sum(case when differensMIN between 0 and 60
                            then 1 else null end)  as To1H
         , DisplayName
         , sum(case when differensMIN between 0 and 240
                            then 1 else null end)  as To4H
         , DisplayName
         , sum(case when differensMIN >= 0 
                            then 1 else null end) as allH
    
    FROM __Deprate 
    group by To1H.DisplayName
    order by To1H.DisplayName

Posting Permissions

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