additional column not in group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

additional column not in group by

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2007-05-29 : 14:08:37

How do I add an additional column (expense_code in this case) in my select list that is not in the group by? If I add the column in the group by then I get the wrong result since it will group by the expense_code.

Here is the result I want

lastname1 firstname1 4201 3016.0659
lastname2 firstname2 4201 2761.2142
lastname3 firstname3 4201 3676.1356
lastname4 firstname4 4201 2037.0128
lastname5 firstname5 4201 1915.5593
lastname6 firstname6 4201 5590.9921
lastname7 firstname7 4201 3884.5576
lastname8 firstname8 4201 4310.4064
lastname9 firstname9 4201 -416.96
lastname10 firstname10 4201 643.6184

This is what I get
lastname1 firstname1 4205 39.16
lastname1 firstname1 4210 1187.5198
lastname1 firstname1 4215 29.5796
lastname1 firstname1 4220 -5.7697
lastname1 firstname1 4225 948.6195
lastname1 firstname1 4230 59.3495
lastname1 firstname1 4250 56.1695
lastname1 firstname1 4255 23.049
lastname1 firstname1 4260 678.3887
lastname2 firstname2 4205 25.28
lastname2 firstname2 4210 1439.8697
lastname2 firstname2 4215 21.8289
lastname2 firstname2 4220 -3.71
lastname2 firstname2 4225 682.1195

Here is my query with the expense_code in the group by
SELECT last_name,first_name,expense_code,
SUM
(a.earnings_amount)
as amount

FROM pr_payroll_charges a,
dbo.bud_stars_object b,
pr_employee_detail c,
pr_employee_master d,
pr_transaction_master e
WHERE a.pca ='19000'
AND datepart(yy, e.Effective_date) = '2007'
AND a.expense_code= b.object_code
AND c.ssn=d.ssn
AND a.ssn=c.ssn
AND a.pca=c.pca
AND a.payroll_no=c.payroll_no
AND a.transaction_id=e.transaction_id
AND a.pay_date=c.pay_date
AND substring(expense_code,1,2)='42'
GROUP BY last_name,first_name,expense_code

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-29 : 15:37:13

Use MAX(ExpenseCode) or MIN(ExpenseCode), depending upon which one you want to display.

e4 d5 xd5 Nf6

forwheeler
Starting Member

44 Posts

Posted - 2007-05-29 : 17:05:52

I simplified this query the first time since I thought there might be an easy answer to this question.
I will post the full query in hopes that I can better explain what I am trying to do.
For each person, I want to sum all their misc. payroll benefit amounts which are all a 42xx code on one row. I want this to display as a '4201' regardless of the original code.
The next row I want to sum all the employee salaries as the real code. These will be a 41xx code.

Here is the full query:

SELECT last_name,first_name,'4201',
SUM(a.earnings_amount) as amount

FROM pr_payroll_charges a,
bud_object b,
pr_employee_detail c,
pr_employee_master d,
pr_transaction_master e
WHERE a.pcc ='19000'
AND datepart(yy, e.Effective_date) = '2007'
AND a.expense_so= b.object_code
AND c.ssn=d.ssn
AND a.ssn=c.ssn
AND a.pcn=c.pcn
AND a.payroll_no=c.payroll_no
AND a.transaction_id=e.transaction_id
AND a.pay_date=c.pay_date
AND substring(expense_so,1,2)='42'
GROUP BY last_name,first_name

UNION

SELECT last_name,first_name,null,
SUM(a.earnings_amount) as amount

FROM pr_payroll_charges a,
bud_object b,
pr_employee_detail c,
pr_employee_master d,
pr_transaction_master e
WHERE a.pcc ='19000'
AND datepart(yy, e.Effective_date) = '2007'

AND a.expense_so= b.object_code
AND c.ssn=d.ssn
AND a.ssn=c.ssn
AND a.pcn=c.pcn
AND a.payroll_no=c.payroll_no
AND a.transaction_id=e.transaction_id
AND a.pay_date=c.pay_date
AND substring(expense_so,1,2)='41'
GROUP BY last_name, first_name

Using this query I get:

LASTNAME1 FIRSTNAME1 NULL 1000.0500
LASTNAME1 FIRSTNAME1 4201 2035.0128
LASTNAME2 FIRSTNAME2 4201 3676.1356
LASTNAME3 FIRSTNAME3 NULL 6560.0200
LASTNAME3 FIRSTNAME3 4201 2759.2142
LASTNAME4 FIRSTNAME4 NULL 1501.9803
LASTNAME4 FIRSTNAME4 4201 5596.9921

What I want is:

LASTNAME1 FIRSTNAME1 4102 10000.0500
LASTNAME1 FIRSTNAME1 4201 2035.0128
LASTNAME2 FIRSTNAME2 4201 3676.1356
LASTNAME3 FIRSTNAME3 4102 6565.0200
LASTNAME3 FIRSTNAME3 4201 2761.2142
LASTNAME4 FIRSTNAME4 4103 1599.9803
LASTNAME4 FIRSTNAME4 4201 5590.9921

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-29 : 19:21:44

First code like a pro and use standard table joins instead of joining tables in the WHERE clause. Then do yourself a favor along with anybody else who has to debug your code a year from now, and dump the meaningless aliases. If you must use aliases, use descriptive ones. Using "a", "b", and "c" is just lazy. Like this:


SELECT   last_name,
         first_name,
         '4201',
         SUM(pr_payroll_charges.earnings_amount) as amount
FROM     pr_payroll_charges
         inner join bud_object
           on pr_payroll_charges.expense_so = bud_object.object_code
         inner join pr_employee_detail
           on pr_payroll_charges.ssn = pr_employee_detail.ssn
              and pr_payroll_charges.pcn = pr_employee_detail.pcn
              and pr_payroll_charges.payroll_no = pr_employee_detail.payroll_no
              and pr_payroll_charges.pay_date = pr_employee_detail.pay_date
         inner join pr_employee_master
           on pr_employee_detail.ssn = pr_employee_master.ssn
         inner join pr_transaction_master
           on pr_payroll_charges.transaction_id = pr_transaction_master.transaction_id
WHERE    pr_payroll_charges.pcc = '19000'
         AND datepart(yy,pr_transaction_master.Effective_date) = '2007'
         AND substring(expense_so,1,2) = '42'
GROUP BY last_name,first_name
UNION
SELECT   last_name,
         first_name,
         null,
         SUM(pr_payroll_charges.earnings_amount) as amount
FROM     pr_payroll_charges
         inner join bud_object
           on pr_payroll_charges.expense_so = bud_object.object_code
         inner join pr_employee_detail
           on pr_payroll_charges.ssn = pr_employee_detail.ssn
              and pr_payroll_charges.pcn = pr_employee_detail.pcn
              and pr_payroll_charges.payroll_no = pr_employee_detail.payroll_no
              and pr_payroll_charges.pay_date = pr_employee_detail.pay_date
         inner join pr_employee_master
           on pr_employee_detail.ssn = pr_employee_master.ssn
         inner join pr_transaction_master
           on pr_payroll_charges.transaction_id = pr_transaction_master.transaction_id
WHERE    pr_payroll_charges.pcc = '19000'
         AND datepart(yy,pr_transaction_master.Effective_date) = '2007'
         AND substring(expense_so,1,2) = '41'
GROUP BY last_name,first_name

Finally, I don't understand how you hope to get values such as 4102 and 4103 in your result set when you have NULL and 4201 hard-coded into your statement. Please explain the business rules that would result in "4103". It may be possible to dump the UNION statement and run this as a single select.

e4 d5 xd5 Nf6

forwheeler
Starting Member

44 Posts

Posted - 2007-05-30 : 09:37:45

I agree with your assessment of the joins and aliases. The main selects are someone elses code that I added the union hoping I could get the results I wanted. I should have cleaned the code before I posted it.

All salaries are 41xx codes. A 4101 would be a full time employee and a 4103 would be a temp employee.
All benefits are 42xx codes.

The table contains weekly salaries that I want to sum into a total amount for each employee and also return their code number.

The table also contains their weekly benefits that I want to sum into a total amount and display this as a 4201 regardless of the original code.

I would like to return a record set such as this grouped by employee.
LASTNAME1 FIRSTNAME1 4102 10000.0500
LASTNAME1 FIRSTNAME1 4201 2035.0128
LASTNAME2 FIRSTNAME2 4201 3676.1356
LASTNAME3 FIRSTNAME3 4102 6565.0200
LASTNAME3 FIRSTNAME3 4201 2761.2142

I can return the code in the query but I am forced to group by the code. I don't want to group by the code since that will return each code and the sum for each code individually instead of the sum of all 42xx and then the sum of all 41xx amounts.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-30 : 11:50:55

If this does not give you what you want, then supply some sample data with the desired output.


SELECT   last_name,
         first_name,
         '4201',
         SUM(pr_payroll_charges.earnings_amount) as amount
FROM     pr_payroll_charges
         inner join bud_object
           on pr_payroll_charges.expense_so = bud_object.object_code
         inner join pr_employee_detail
           on pr_payroll_charges.ssn = pr_employee_detail.ssn
              and pr_payroll_charges.pcn = pr_employee_detail.pcn
              and pr_payroll_charges.payroll_no = pr_employee_detail.payroll_no
              and pr_payroll_charges.pay_date = pr_employee_detail.pay_date
         inner join pr_employee_master
           on pr_employee_detail.ssn = pr_employee_master.ssn
         inner join pr_transaction_master
           on pr_payroll_charges.transaction_id = pr_transaction_master.transaction_id
WHERE    pr_payroll_charges.pcc = '19000'
         AND datepart(yy,pr_transaction_master.Effective_date) = '2007'
         AND substring(expense_so,1,2) = '42'
GROUP BY last_name,first_name
UNION
SELECT   last_name,
         first_name,
         expense_so,
         SUM(pr_payroll_charges.earnings_amount) as amount
FROM     pr_payroll_charges
         inner join bud_object
           on pr_payroll_charges.expense_so = bud_object.object_code
         inner join pr_employee_detail
           on pr_payroll_charges.ssn = pr_employee_detail.ssn
              and pr_payroll_charges.pcn = pr_employee_detail.pcn
              and pr_payroll_charges.payroll_no = pr_employee_detail.payroll_no
              and pr_payroll_charges.pay_date = pr_employee_detail.pay_date
         inner join pr_employee_master
           on pr_employee_detail.ssn = pr_employee_master.ssn
         inner join pr_transaction_master
           on pr_payroll_charges.transaction_id = pr_transaction_master.transaction_id
WHERE    pr_payroll_charges.pcc = '19000'
         AND datepart(yy,pr_transaction_master.Effective_date) = '2007'
         AND substring(expense_so,1,2) = '41'
GROUP BY last_name,first_name, expense_so

e4 d5 xd5 Nf6

forwheeler
Starting Member

44 Posts

Posted - 2007-05-30 : 12:58:26

Thanks blindman, that works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources