Link to home
Create AccountLog in
Avatar of pborregg
pborreggFlag for United States of America

asked on

Problem with COUNT MS SQL Server 2005

I have the following code in SQL:

SELECT a.eid, a.first_name, a.last_name, a.bus_email_1, a.bus_phn_1, COUNT(b.exp_id) as recCnt, SUM(c.amt) as TtlAmt
FROM hr_expense_sub c, hr_employee_info a, hr_expense_main b
WHERE b.sup_eid = MM_EID AND a.eid = b.staff_eid AND c.exp_id = b.exp_id GROUP BY a.eid, a.first_name, a.last_name, a.bus_email_1, a.bus_phn_1

the result is below:

EID        First Name   Last Name   Bus Email 1             Bus Phn 1          recCnt    TtlAmt
ASD     Al                 Denton        301-555-1212    45           5452.55
DFD     Dan               Donalds         410-555-1212       5             515.23
DFE     Donald          Edwards         240-555-1212       2            1575.22

The problem is that the recCnt Column is returning the total records in the hr_expense_sub table and I want to only return the number of records in the hr_expense_main table.  45 is the total number of records that are LINE ITEMS in the sub table... for ASD is 7; DFD is 2 and DFE is 1.  I do not wish to return the total sub records, just the total records from the main table not the sub table; however, I DO wish to return the total amount of ALL records from the sub table to show what each expense record totals... so the TtlAmt IS correct.

Where am I going wrong?

Again, for example the First Record above has 7 PARENT records and those 7 PARENT records have a Total of 45 COMBINED child records split between them.  Does that make sense?

Thanks in advance,

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pborregg


Thank you .... it works PERFECTLY!

Happy New Year!