group by question using one of your samples

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 2008 Forums

Transact-SQL (2008)

group by question using one of your samples

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2015-02-17 : 20:40:40

I need to use a group by where the aggregate does not contain all the fields in the select statement. I found roland bouman's blog with the sample:

SELECT
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
FROM
Customers C
INNER JOIN
(SELECT
CustomerID, SUM(Sales) as TotalSales
FROM
Sales
GROUP BY
CustomerID) S
ON
C.CustomerID = S.CustomerID

I wanted to modify it as follows:
both tables are table variables #Customers and #Sales and the join criteria consists of 2 or more fields in each table.

I used the same syntax to create this using only 2 fields:

SELECT
C.CustomerID, C.tagID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
FROM
#Customers C
INNER JOIN
(SELECT
CustomerID, TagID, SUM(Sales) as TotalSales
FROM
#Sales
GROUP BY
CustomerID,TagID) S
ON
C.CustomerID = S.CustomerID, c.tagID = f.tagid

the error says incorrect syntax on = and this is in reference to the 'on statement'.

I believe I need different syntax for table variables. Can someone show me how I would do the same procedure with the changes I made.

thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 22:35:40

The comma in the ON clause should be AND

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources