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
  • Last Modified:

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
Asked:
andy_booth
  • 2
1 Solution
 
hans_vdCommented:
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
 
andy_boothAuthor 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
 
LowfatspreadCommented:
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
 
andy_boothAuthor Commented:
Works like a charm, thankyou very much
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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