this sql does not work for group correctly

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

General SQL Server Forums

New to SQL Server Programming

this sql does not work for group correctly

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-21 : 13:10:39

the @sql is not grouping by category so the report is showing incorrectly. we changed the database is what happened so something is messed up in the mapping from old to new and i can't see it.

SET @SQL = 'SELECT SUM(dbo.Transactions.Cost) AS Price, SUM(dbo.Transactions.Quantity) AS quantity, dbo.Categories.CategoryName AS CatD, dbo.Items.ItemName AS ITEMDESC,
dbo.Items.ItemName AS ITMGEDSC, SUM(dbo.Transactions.Cost) / SUM(dbo.Transactions.Quantity) AS UNITPRCE,
dbo.Departments.DepartmentName AS USRDEF05
FROM dbo.Transactions INNER JOIN
dbo.Categories ON dbo.Transactions.CategoryId = dbo.Categories.CategoryId INNER JOIN
dbo.Items ON dbo.Transactions.ItemId = dbo.Items.ItemId INNER JOIN
dbo.Departments ON dbo.Transactions.DepartmentId = dbo.Departments.DepartmentId AND
dbo.Categories.DepartmentId = dbo.Departments.DepartmentId INNER JOIN
dbo.Customers ON dbo.Transactions.CustomerId = dbo.Customers.CustomerId AND dbo.Departments.CustomerId = dbo.Customers.CustomerId

Where dbo.Transactions.DateCreated between ''' + @FromDate + ''' and ''' + @ToDate + ''''

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.Categories.CategoryName in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.Departments.DepartmentName in (' + @Department + ')'
END

IF @CustName <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.Customers.CustomerName in (' + @CustName + ')'
END

SET @SQL = @SQL + ' GROUP BY dbo.Categories.CategoryName, dbo.Items.ItemName, dbo.Departments.DepartmentName'
--print @SQL
EXEC (@SQL)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 14:03:48

If you can't see why it isn't grouping correctly, we have no chance to. You provide no structure information, no sample data, no desired vs actual results.

Please provide more information...you "changed the database so something is messed up". How do you propose we determine what that would be based on a SQL statement we can't execute?

Pretend for a moment, that you don't know anything about what you are doing or what your problem is and read that post again....then, post information that would allow someone unfamiliar with your situation help you.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 10:43:33

as of now your query is not grouping by CategoryName alone but by composite combination dbo.Categories.CategoryName, dbo.Items.ItemName, dbo.Departments.DepartmentName so if there are multiple item, departments existing per category it will return multiple rows per category. if you could show what output you want, we could suggest how GROUP BY should be

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources