Professional Web Applications Themes

GROUP BY Problem - Microsoft SQL / MS SQL Server

I'm having trouble trying to write a select statement that would make my data in in Figure 1 look like Figure 2. Being that Shift1,Shift2,Shift3 aren't fields, but rather data from the Shift field, how can I achieve this? (I omitted extra joins, etc. for simplicity) FIGURE 1 (below works fine) SELECT dtDate, Shift, SUM(Downtime) as SumDowntime FROM tblDowntime GROUP BY dtDate, Shift ORDER BY Shift, MIN(dtDate) dtDate Shift SumDowntime ======================== 1/1/2003 Shift1 4 2/1/2003 Shift1 5 3/1/2003 Shift1 7 1/1/2003 Shift2 8 2/1/2003 Shift2 2 3/1/2003 Shift2 6 1/1/2003 Shift3 3 2/1/2003 Shift3 11 3/1/2003 Shift3 9 FIGURE 2 ...

  1. #1

    Default GROUP BY Problem

    I'm having trouble trying to write a select statement that would make my
    data in in Figure 1 look like Figure 2. Being that Shift1,Shift2,Shift3
    aren't fields, but rather data from the Shift field, how can I achieve this?
    (I omitted extra joins, etc. for simplicity)

    FIGURE 1 (below works fine)

    SELECT dtDate, Shift, SUM(Downtime) as SumDowntime
    FROM tblDowntime
    GROUP BY dtDate, Shift
    ORDER BY Shift, MIN(dtDate)

    dtDate Shift SumDowntime
    ========================
    1/1/2003 Shift1 4
    2/1/2003 Shift1 5
    3/1/2003 Shift1 7
    1/1/2003 Shift2 8
    2/1/2003 Shift2 2
    3/1/2003 Shift2 6
    1/1/2003 Shift3 3
    2/1/2003 Shift3 11
    3/1/2003 Shift3 9



    FIGURE 2 (can't find sql syntax to make this happen)

    dtDate Shift1 Shift2 Shift3
    ==========================
    1/1/2003 4 8 3
    2/1/2003 5 2 11
    3/1/2003 7 6 9


    Scott Guest

  2. #2

    Default Re: GROUP BY Problem

    SELECT dtdate,
    SUM(CASE shift WHEN 'Shift1' THEN downtime END) AS shift1,
    SUM(CASE shift WHEN 'Shift2' THEN downtime END) AS shift2,
    SUM(CASE shift WHEN 'Shift3' THEN downtime END) AS shift3
    FROM tblDowntime
    GROUP BY dtdate
    ORDER BY dtdate

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: GROUP BY Problem

    I tried using CASE and now come up with an aggregate error. Consider the
    below code. Figure 1 doesn't work with CASE, but Figure 2 does work with
    only the CASE part substituted.


    FIGURE 1 (doesn't work, only difference is CASE part)

    SELECT TOP 100 PERCENT CAST(DATEPART(m, dbo.tblDowntime.StartDateTime)
    AS varchar(2)) + '-' + CAST(DATEPART(yyyy, dbo.tblDowntime.StartDateTime)
    AS char(4)) AS [Date],
    SUM(CASE WHEN dbo.tblDowntime.StartShiftID = '1' THEN SUM(CAST(DATEDIFF(s,
    dbo.tblDowntime.StartDateTime, dbo.tblDowntime.EndDateTime) AS float(8))/
    3600) END) AS shift1,
    SUM(CASE WHEN dbo.tblDowntime.StartShiftID = '2' THEN SUM(CAST(DATEDIFF(s,
    dbo.tblDowntime.StartDateTime, dbo.tblDowntime.EndDateTime) AS float(8))/
    3600) END) AS shift2,
    SUM(CASE WHEN dbo.tblDowntime.StartShiftID = '3' THEN SUM(CAST(DATEDIFF(s,
    dbo.tblDowntime.StartDateTime, dbo.tblDowntime.EndDateTime) AS float(8))/
    3600) END) AS shift3
    FROM dbo.tblDowntime LEFT OUTER JOIN
    dbo.tblShift ON dbo.tblDowntime.StartShiftID =
    dbo.tblShift.ID LEFT OUTER JOIN
    dbo.t_teams INNER JOIN
    dbo.t_machinecenter ON dbo.t_teams.teamID =
    dbo.t_machinecenter.teamID LEFT OUTER JOIN
    dbo.tblDTCategory ON dbo.t_machinecenter.machineID =
    dbo.tblDTCategory.machineID ON
    dbo.tblDowntime.DTCategoryID = dbo.tblDTCategory.ID
    WHERE (dbo.tblDowntime.DTDefID = '1')

    GROUP BY CAST(DATEPART(m, dbo.tblDowntime.StartDateTime) AS varchar(2)) +
    '-' + CAST(DATEPART(yyyy, dbo.tblDowntime.StartDateTime) AS char(4))

    ORDER BY MIN(dbo.tblDowntime.StartDateTime)



    FIGURE 2 (works)

    SELECT TOP 100 PERCENT CAST(DATEPART(m, dbo.tblDowntime.StartDateTime)
    AS varchar(2)) + '-' + CAST(DATEPART(yyyy, dbo.tblDowntime.StartDateTime)
    AS char(4)) AS [Date], SUM(CAST(DATEDIFF(s,
    dbo.tblDowntime.StartDateTime, dbo.tblDowntime.EndDateTime) AS float(8)) /
    3600) AS Downtime
    FROM dbo.tblDowntime LEFT OUTER JOIN
    dbo.tblShift ON dbo.tblDowntime.StartShiftID =
    dbo.tblShift.ID LEFT OUTER JOIN
    dbo.t_teams INNER JOIN
    dbo.t_machinecenter ON dbo.t_teams.teamID =
    dbo.t_machinecenter.teamID LEFT OUTER JOIN
    dbo.tblDTCategory ON dbo.t_machinecenter.machineID =
    dbo.tblDTCategory.machineID ON
    dbo.tblDowntime.DTCategoryID = dbo.tblDTCategory.ID
    WHERE (dbo.tblDowntime.DTDefID = '1')
    GROUP BY CAST(DATEPART(m, dbo.tblDowntime.StartDateTime) AS varchar(2)) +
    '-' + CAST(DATEPART(yyyy, dbo.tblDowntime.StartDateTime) AS char(4))
    ORDER BY MIN(dbo.tblDowntime.StartDateTime)







    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Scott Guest

  4. #4

    Default GROUP BY Problem

    Here is the answer use CASE to pivot data:
    SELECT dtdate,MAX(CASE WHEN Shift='Shift1' THEN
    SumDowntime)
    AS Shift1,MAX(CASE WHEN Shift='Shift2' THEN SumDowntime)
    AS Shift2,MAX(CASE WHEN Shift='Shift3' THEN SumDowntime)
    AS Shift3
    GROUP BY dtdate


     
    that would make my 
    Shift1,Shift2,Shift3 
    can I achieve this? 
    Laurent Guest

  5. #5

    Default Re: GROUP BY Problem

    Thanks for the eyes. About 10 minutes after I posted, I noticed the double
    SUM's.

    Thanks for everyone's input.

    "David Portas" <org> wrote in message
    news:%phx.gbl... 
    extra 
    dbo.tblDowntime.EndDateTime) 
    relevant [/ref]
    dbo.tblDowntime.StartDateTime) [/ref]
    dbo.tblDowntime.StartDateTime) 
    > SUM(CAST(DATEDIFF(s, [/ref]
    float(8))/ 
    > SUM(CAST(DATEDIFF(s, [/ref]
    float(8))/ 
    > SUM(CAST(DATEDIFF(s, [/ref]
    float(8))/ [/ref]
    = [/ref]
    dbo.tblDTCategory.ID [/ref]
    + [/ref]
    dbo.tblDowntime.StartDateTime) [/ref]
    dbo.tblDowntime.StartDateTime) [/ref]
    / [/ref]
    = [/ref]
    dbo.tblDTCategory.ID [/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Scott Guest

Similar Threads

  1. Query Using Group Problem
    By rpmindllc in forum Coldfusion Database Access
    Replies: 17
    Last Post: August 20th, 08:29 PM
  2. Group By problem
    By Dim in forum MySQL
    Replies: 14
    Last Post: March 20th, 09:53 PM
  3. Problem with SUM and GROUP
    By SuNcO in forum MySQL
    Replies: 3
    Last Post: September 13th, 05:42 PM
  4. Sum and Group problem with MSSQL
    By zu in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 2nd, 04:11 PM
  5. Group by DATE problem
    By Yankeet in forum Macromedia ColdFusion
    Replies: 3
    Last Post: October 14th, 03:24 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