Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 343

# Group By Problem

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],
[Journal Data].[2150 Pay Adjustment]

HAVING
(((Sum(([Journal Data].[2000 Basic Salary]+[Journal Data].[2150 Pay Adjustment])*0.15))<>0) AND [Journal Data].[Month] = '0602')
0
andy_booth
• 2
1 Solution

Commented:
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

Author Commented:
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

Commented:
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
Sum(([2000 Basic Salary]+[2150 Pay Adjustment])*0.15)<>0
0

Author Commented:
Works like a charm, thankyou very much
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.