Solved

# Group By Problem

Posted on 2006-04-06
339 Views
Basically I wanted to change my query to have a case statement.

Once I did that, I needed to add group by for two more columns, but its now showing me too many rows as I only want to see data grouped by cost centre and month.

How can I have the case statement but not have them in a group by?

SELECT
[Journal Data].Company,
[Journal Data].[Month],
'0000' AS [Cost Centre],
'232000' AS Account,
CASE
WHEN [2000 Basic Salary]+[2150 Pay Adjustment] >= 4167
THEN Sum(([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.20)
ELSE Sum(([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.15)
END AS Credit,

'0' AS Debit,
'Payroll ' + [Journal Data].[Month] AS [Journal Name],
'Payroll Interface ' + [Journal Data].[Month] AS [Line Description],
'Bonus 2003 ' AS [Reconciliation Reference]
FROM
[Journal Data]
GROUP BY
[Journal Data].Company,
[Journal Data].[Month],
[Journal Data].[2000 Basic Salary],

HAVING
(((Sum(([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.15))<>0) AND [Journal Data].[Month] = '0602')
0
Question by:andy_booth

LVL 6

Expert Comment

I'm not sure if this is possible, but what happens if you try putting the SUM around the case statement instead of in it?  Can you remove the two fields from the group by then?

Just an idea...

Sum(
CASE
WHEN [2000 Basic Salary]+[2150 Pay Adjustment] >= 4167
THEN ([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.20
ELSE ([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.15
END AS Credit
)
0

LVL 1

Author Comment

It's not happy at all now.

Server: Msg 195, Level 15, State 10, Line 12
'SUM' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'Sum'.
0

LVL 50

Accepted Solution

SELECT
Company,
[Month],
'0000' AS [Cost Centre],
'232000' AS Account,
sum(([2000 Basic Salary]+[2150 Pay Adjustment]) *
CASE
WHEN [2000 Basic Salary]+[2150 Pay Adjustment] >= 4167
THEN 0.20
ELSE 0.15
END) AS Credit,

'0' AS Debit,
'Payroll ' + [Month] AS [Journal Name],
'Payroll Interface ' + [Month] AS [Line Description],
'Bonus 2003 ' AS [Reconciliation Reference]
FROM
[Journal Data]
Where [Month] = '0602'
GROUP BY
Company,
[Month]

HAVING
0

LVL 1

Author Comment

Works like a charm, thankyou very much
0

## Featured Post

### Suggested Solutions

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed