grouping by an alias 2

[toksoladuti]

I have the following statement, which returns what I need:

SELECT B1.Name AS Branch,
(DATENAME(dw, OH.DateTimeCreated)) as [Day Created],
(DATEPART(hour, OH.DateTimeCreated)) as [Hour Created],
OPT.Name AS HowPlaced,
NVL1.Name AS SaleType,
(OH.TotalSellPrice - OH.TotalCostPrice) AS TotalProfit,
OH.TotalSellPrice, OH.OrderID AS ID1
FROM OrderHeader AS OH WITH(NOLOCK)
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = OH.BranchID
LEFT JOIN OrderPlacementType AS OPT WITH(NOLOCK) ON OPT.OrderPlacementTypeID = OH.OrderPlacementTypeID
LEFT JOIN NamedValue AS NVL1 WITH(NOLOCK) ON (NVL1.ValueListName = 'disOrderSaleTypes' AND NVL1.Value = OH.SaleType)

However, I need to GROUP BY the hour created, which is pulled by:

(DATEPART(hour, OH.DateTimeCreated)) as [Hour Created]

I've tried just grouping by this statement but it doesn't work. I'm not too hot on SQL so any pointers would be greatly appreciated.

Many thanks.

 

[hmckillop]

why does this not work

SELECT B1.Name AS Branch, 
(DATENAME(dw, OH.DateTimeCreated)) as [Day Created],
(DATEPART(hour, OH.DateTimeCreated)) as [Hour Created],
OPT.Name AS HowPlaced, 
NVL1.Name AS SaleType, 
(OH.TotalSellPrice - OH.TotalCostPrice) AS TotalProfit, 
OH.TotalSellPrice, OH.OrderID AS ID1
FROM OrderHeader AS OH WITH(NOLOCK)
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = OH.BranchID
LEFT JOIN OrderPlacementType AS OPT WITH(NOLOCK) ON OPT.OrderPlacementTypeID = OH.OrderPlacementTypeID
LEFT JOIN NamedValue AS NVL1 WITH(NOLOCK) ON (NVL1.ValueListName = 'disOrderSaleTypes' AND NVL1.Value = OH.SaleType)
GROUP BY 
(DATEPART(hour, OH.DateTimeCreated))

"I'm living so far beyond my income that we may almost be said to be living apart

 

[toksoladuti]

Thanks for the reply. I get the following error:

Msg 8120, Level 16, State 1, Line 1
Column 'Branch.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks.

 

[hmckillop]

If you have a series of fields in your select they must also be included in the GROUP by, unless you have an agregate function e.g. SUM, MAX, MIN, AVG etc.

So if you need to group by then you need to include this field.

Having read your original question, you need to group by "Hour Created", what do you need to group. E.g. Do you want to know how many people placed orders of a certain type per hour per day . if so then you need

SELECT count(*),
B1.Name AS Branch,
(DATENAME(dw, OH.DateTimeCreated)) as [Day Created],
(DATEPART(hour, OH.DateTimeCreated)) as [Hour Created],
OPT.Name AS HowPlaced,
NVL1.Name AS SaleType,
SUM((OH.TotalSellPrice - OH.TotalCostPrice)) AS TotalProfit,
SUM(OH.TotalSellPrice) , OH.OrderID AS ID1
FROM OrderHeader AS OH WITH(NOLOCK)
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = OH.BranchID
LEFT JOIN OrderPlacementType AS OPT WITH(NOLOCK) ON OPT.OrderPlacementTypeID = OH.OrderPlacementTypeID
LEFT JOIN NamedValue AS NVL1 WITH(NOLOCK) ON (NVL1.ValueListName = 'disOrderSaleTypes' AND NVL1.Value = OH.SaleType)
GROUP BY
B1.Name ,
(DATENAME(dw, OH.DateTimeCreated))
(DATEPART(hour, OH.DateTimeCreated))
OPT.Name ,
NVL1.Name , OH.OrderID
[/code]

If this is not what you want, please explain the exact details of what you wish to output, a sample data set of orginal data and expected output format and then we should be able to put something together

"I'm living so far beyond my income that we may almost be said to be living apart

 

[toksoladuti]

Thanks hmckillop. I ended up using the following statement which did the trick:

SELECT B1.Name AS Branch,
(DATEPART(hour, OH.DateTimeCreated)) as [Hour Created],
(DATEPART(day, OH.DateTimeCreated)) as [Day Created],
(DATENAME(month, OH.DateTimeCreated)) as [Month Created],
(DATEPART(year, OH.DateTimeCreated)) as [Year Created],
SUM((OH.TotalSellPrice - OH.TotalCostPrice)) AS [Total Profit Value (£)],
SUM(OH.TotalSellPrice) AS [Total Sales Value (£)]
FROM OrderHeader AS OH WITH(NOLOCK)
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = OH.BranchID
LEFT JOIN OrderPlacementType AS OPT WITH(NOLOCK) ON OPT.OrderPlacementTypeID = OH.OrderPlacementTypeID
LEFT JOIN NamedValue AS NVL1 WITH(NOLOCK) ON (NVL1.ValueListName = 'disOrderSaleTypes' AND NVL1.Value = OH.SaleType)
WHERE (DATENAME(dw, OH.DateTimeCreated))='Saturday'
GROUP BY (DATEPART(hour, OH.DateTimeCreated)),
(DATEPART(day, OH.DateTimeCreated)), MONTH(OH.DateTimeCreated),
(DATENAME(month, OH.DateTimeCreated)), (DATEPART(year, OH.DateTimeCreated)), b1.name
ORDER BY
b1.name, (DATEPART(year, OH.DateTimeCreated))DESC, MONTH(OH.DateTimeCreated) DESC, (DATENAME(month, OH.DateTimeCreated)) DESC, (DATEPART(day, OH.DateTimeCreated)) DESC, (DATEPART(hour, OH.DateTimeCreated)) ASC

Many thanks.