• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Group By & Sum MS SQL Query

I am not sure if I am doing this right.. but I want to write a query that can do a couple things..

I have a table with Employee ID, Date/Time and Charge amount for an employee Cafeteria.

I need to be able to query the database between two dates, say 3/1/12 and 3/15/12 and show the charges for Employee ID 1 and sum the total of purchases..

The Query to get the total is where I am running into issues.. to me this should work but it does not.

SELECT     PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, 
                      SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM         PAYROLL.dbo.EMPLOYEE RIGHT OUTER JOIN
                      EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID
HAVING      (EMP_DEDUCTIONS.VOID IS NULL) AND (PAYROLL.dbo.EMPLOYEE.ID = 'BEA01') AND (CONVERT(varchar, EMP_DEDUCTIONS.CHARGE_DATETIME, 
                      101) BETWEEN '3/1/2012' AND '3/15/2012')
ORDER BY PAYROLL.dbo.EMPLOYEE.ID

Open in new window


I get this error

Msg 8121, Level 16, State 1, Line 6
Column 'EMP_DEDUCTIONS.CHARGE_DATETIME' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 6
Column 'EMP_DEDUCTIONS.CHARGE_DATETIME' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

This will ultimately be written for a web page so the employee can run a report on their purchases and get the total.

Thanks for any help.
0
DMTechGrooup
Asked:
DMTechGrooup
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
add CHARGE_DATETIME to the group by clause:

SELECT     PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, 
                      SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM         PAYROLL.dbo.EMPLOYEE RIGHT OUTER JOIN
                      EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID, EMP_DEDUCTIONS.CHARGE_DATETIME 
HAVING      (EMP_DEDUCTIONS.VOID IS NULL) AND (PAYROLL.dbo.EMPLOYEE.ID = 'BEA01') AND (CONVERT(varchar, EMP_DEDUCTIONS.CHARGE_DATETIME, 
                      101) BETWEEN '3/1/2012' AND '3/15/2012')
ORDER BY PAYROLL.dbo.EMPLOYEE.ID

Open in new window

0
 
tigin44Commented:
try this

SELECT     PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME,
                      SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM         PAYROLL.dbo.EMPLOYEE RIGHT OUTER JOIN
                      EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
WHERE      (EMP_DEDUCTIONS.VOID IS NULL)
  AND (PAYROLL.dbo.EMPLOYEE.ID = 'BEA01')
  AND (CONVERT(varchar, EMP_DEDUCTIONS.CHARGE_DATETIME, 101) BETWEEN '3/1/2012' AND '3/15/2012')
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID
ORDER BY PAYROLL.dbo.EMPLOYEE.ID
0
 
DMTechGrooupAuthor Commented:
Trying this

SELECT     PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, 
                      SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM         PAYROLL.dbo.EMPLOYEE RIGHT OUTER JOIN
                      EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
WHERE      (EMP_DEDUCTIONS.VOID IS NULL) 
  AND (PAYROLL.dbo.EMPLOYEE.ID = 'BEA01') 
  AND (CONVERT(varchar, EMP_DEDUCTIONS.CHARGE_DATETIME, 101) BETWEEN '3/1/2012' AND '3/15/2012')
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID
ORDER BY PAYROLL.dbo.EMPLOYEE.ID

Open in new window


I don't get an error but I also get no results.  Same with just adding the other to the group by, still no results.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
OCDanCommented:
Try this mate:
SELECT     PAYROLL.dbo.EMPLOYEE.ID,
               PAYROLL.dbo.EMPLOYEE.LAST_NAME,
               PAYROLL.dbo.EMPLOYEE.FIRST_NAME,
               SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM  PAYROLL.dbo.EMPLOYEE
RIGHT OUTER JOIN EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
WHERE  EMP_DEDUCTIONS.VOID IS NULL
  AND PAYROLL.dbo.EMPLOYEE.ID = 'BEA01'
  AND EMP_DEDUCTIONS.CHARGE_DATETIME BETWEEN '2012-03-01 00:00:00' AND '2012-03-15 23:59:59')
 
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID
ORDER BY PAYROLL.dbo.EMPLOYEE.ID
0
 
tigin44Commented:
so try this

SELECT     PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME,
                      SUM(EMP_DEDUCTIONS.CHARGE_AMOUNT) AS Total
FROM         PAYROLL.dbo.EMPLOYEE RIGHT OUTER JOIN
                      EMP_DEDUCTIONS ON PAYROLL.dbo.EMPLOYEE.ID = EMP_DEDUCTIONS.EMP_ID
WHERE      (EMP_DEDUCTIONS.VOID IS NULL)
  AND (PAYROLL.dbo.EMPLOYEE.ID = 'BEA01')
  AND (CONVERT(date, EMP_DEDUCTIONS.CHARGE_DATETIME, 101) BETWEEN '3/1/2012' AND '3/15/2012')
GROUP BY PAYROLL.dbo.EMPLOYEE.ID, PAYROLL.dbo.EMPLOYEE.LAST_NAME, PAYROLL.dbo.EMPLOYEE.FIRST_NAME, EMP_DEDUCTIONS.VOID
ORDER BY PAYROLL.dbo.EMPLOYEE.ID
0
 
DMTechGrooupAuthor Commented:
Last two worked like a charm.. now I will look it over to see where I need to adjust.. thanks for the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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