Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Group By

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:01:16

Hi, i need help with my group by

SELECT

CAST('01/' + CAST(Month(o.orderdate) As varchar) + '/' + CAST(Year(o.orderdate)As VarChar) As DateTime) As FirstDayOfMonth
, oro.Quantity
, CAST(0 As Decimal(17,2)) as Month0

From
dbo.Orders as o
inner join [Order Details] as oro ON o.OrderID = oro.OrderID

--Group by

--CAST('01/' + CAST(Month(o.orderdate) As varchar) + '/' + CAST(Year(o.orderdate)As VarChar)

I want it to group only by the above column

Many thank for the help

M. Ncube

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 16:08:54

Then you will have to aggregate the Quantity in some way. SUM, perhaps?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:12:05

True, i would have to do so.

M. Ncube

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:14:45

But i am not sure how to go about the group by and perhaps with aggregating the other columns to suit the group by.

Thanks in advance.

M. Ncube

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-31 : 16:21:45

So you should be able to group and sum like shown below:

SELECT  CAST('01/' + CAST(MONTH(o.orderdate) AS VARCHAR) + '/'
        + CAST(YEAR(o.orderdate) AS VARCHAR) AS DATETIME) AS FirstDayOfMonth ,
        SUM(oro.Quantity ) ,
        CAST(0 AS DECIMAL(17, 2)) AS Month0
FROM    dbo.Orders AS o
        INNER JOIN [Order Details] AS oro ON o.OrderID = oro.OrderID

Group by
CAST('01/' + CAST(MONTH(o.orderdate) AS VARCHAR) + '/'
        + CAST(YEAR(o.orderdate) AS VARCHAR) AS DATETIME),
CAST(0 AS DECIMAL(17, 2))

You may not need the last line in the group by clause because it is a constant.

Another way to do the date to get the beginning of the month is this:[code]DATEADD(mm,DATEDIFF(mm,0,o.orderdate),0)[code]

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:27:51

Thanks but i am getting the following error from SQL Server.

''Each GROUP BY expression must contain at least one column that is not an outer reference.''

M. Ncube

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 16:46:59

[code]SELECT DATEADD(MONTH, DATEDIFF(MONTH, '17530101', o.OrderDate), '17530101') AS FirstDayOfMonth,
SUM(d.Quantity) AS MonthlySum,
CAST(0 AS DECIMAL(17, 2)) AS Month0
FROM dbo.Orders AS o
INNER JOIN dbo.[Order Details] AS d ON d.OrderID = o.OrderID
GROUP BY DATEDIFF(MONTH, '17530101', o.OrderDate);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 16:50:56

But if you are doing a running monthly total as I suspect, you should know that SQL Server 2012 handles this directly.

-- SwePeso
WITH cteSource(theMonth, theSum)
AS (
	SELECT		DATEDIFF(MONTH, '17530101', o.OrderDate) AS theMonth,
			SUM(d.Quantity) AS theSum
	FROM		dbo.Orders AS o
	INNER JOIN	dbo.[Order Details] AS d ON d.OrderID = o.OrderID
	GROUP BY	DATEDIFF(MONTH, '17530101', o.OrderDate)
)
SELECT	DATEADD(MONTH, theMonth, '17530101') AS FirstDayOfMonth,
	theSum AS MonthlySum,
	SUM(theSum) OVER (ORDER BY theMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM	cteSource;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:56:50

Thanks a lot, its worked. Perfect !

M. Ncube

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-31 : 16:57:58

I am using 2008, Oh didn't notice its subjects for 2012 here. Really appreciate the help.

M. Ncube

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources