CODEWITHSUNDEEP
sqlsql-servergroup-by
Endiss
Endiss

Reputation: 699

Group by in subquery?

I need some help with group by in subquery. Can you help me to solve this grouping problem:

select
  DATEPART(wk, oh. ExportDate) as wk,
  DATEPART(dw,oh.ExportDate) as day,
  ro.Name,
  Pallets = sum(oh.Pallets),
  Box = (select count(Number) from OrderItem where ID_OrderHead = oh.Id) 
from
  OrderHeadPDAEvent ohpe
  left outer join OrderHead oh on oh.Id = ohpe.ID_OrderHeader
  left outer join Route ro on oh.ID_Route = ro.ID
where
  ID_Route = '00000000-0000-0000-0000-000000000000'
    and
  oh.ExportDate > dbo.GetStartOfDay('2012-08-01 14:35:00.000' ) 
    and 
  oh.ExportDate < dbo.GetEndOfDay('2013-08-08 14:35:00.000')
group by 
  oh.ExportDate, ro.Name, oh.ID
order by 
  DATEPART(wk, oh. ExportDate)

And data looks like this:

26  3   Standard - Uten rute    5   49
26  3   Standard - Uten rute    2   45
26  3   Standard - Uten rute    2   38
26  3   Standard - Uten rute    1   26
26  3   Standard - Uten rute    1   64
26  3   Standard - Uten rute    2   45
26  3   Standard - Uten rute    3   64

I want to sum all rows to get them in 1 row only.

Upvotes: 2

Views: 181

Answers (1)

Devart
Devart

Reputation: 121902

Try this one -

SELECT
      DATEPART(wk, oh.ExportDate) AS wk
    , DATEPART(dw, oh.ExportDate) AS day
    , ro.name
    , Pallets = SUM(oh.Pallets)
    , Box = SUM(n)
FROM dbo.OrderHeadPDAEvent ohpe
LEFT JOIN dbo.OrderHead oh ON oh.ID = ohpe.ID_OrderHeader
LEFT JOIN dbo.[Route] ro ON oh.ID_Route = ro.ID
LEFT JOIN (
     SELECT n = COUNT(number), ID_OrderHead
     FROM dbo.OrderItem
     GROUP BY ID_OrderHead 
) t ON t.ID_OrderHead = oh.ID
WHERE ID_Route = '00000000-0000-0000-0000-000000000000'
     AND oh.ExportDate BETWEEN 
                         dbo.GetStartOfDay('2012-08-01 14:35:00.000') 
                         AND 
                         dbo.GetEndOfDay('2013-08-08 14:35:00.000')
GROUP BY
      oh.ExportDate
    , ro.name
ORDER BY wk

Upvotes: 2

Related Questions