Distinct, Unique, Group by, 'Footing'? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Distinct, Unique, Group by, 'Footing'?

Author

Topic

scared
Starting Member

14 Posts

Posted - 2007-06-13 : 13:35:11

I created an ouput text file that was perfect until specs were revised today to add quarterly tax and deduction summations for each employee. (Expected turnaround time for new spec: today!)

So my employee record now has 8 records (which I expected) because I had to join it to the pay_summary table. Like this:

SELECT
COALESCE(CONVERT(char(1),e.record_status),'')
+ COALESCE(CONVERT(char(4),'97 '),'')
and many more fields
from employees as e
JOIN emp_taxes as t on e.employee_no = t.employee_no
LEFT OUTER JOIN pay_summary AS p ON e.employee_no = p.employee_no

Where
dateadd(d, 0, datediff(d, 0, p.dated)) BETWEEN '20061231' AND '20070401'

and e.record_status not like 'D'
and t.tax_authority_type = 'F'
and e.company_no = '2' and e.employee_no = t.employee_no

order by e.employee_no

There are 6 or so fields I intend to sum as records to go into the
file. In another language, in order to get one record per employee_no, the syntax would be like this:

sort on employee_no
footing at employee_no
report field_a field_b field_c subtotal field_e field_f subtotal

in which case fields c and f are numeric and summed from the multiple pay_summary records.

What is the (most efficient) SQL command to give only one record per
employee while summing all the detail records?

I can't thank you guys enough for this forum.

Lisa

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 13:53:43

You can do a GROUP BY on the Employee column and SUM on all the other columns..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scared
Starting Member

14 Posts

Posted - 2007-06-13 : 14:05:22

[quote]Originally posted by dinakar

You can do a GROUP BY on the Employee column and SUM on all the other columns..

Sir, I deleted order by e.employee_no and added

group by e.employee_no and got this error:

ODBC Error 8120: "[Microsoft][ODBC SQL Server Driver][SQL Server]
Column 'e.record_status' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause." (42000)

By adding a GROUP BY clause, do I have to change the nature of all
the other fields I've selected?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 14:14:03

Yes adding a group by does require some changes. You cannot have columns in your SELECT that are not in the group by unless there are aggregate functions such as a MIN(column) or MAX(Column) or SUM(Column) etc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scared
Starting Member

14 Posts

Posted - 2007-06-13 : 14:30:09

So, are you saying I need to put an aggregate (min, sum, max) etc even onto a character field I'm extracting? I changed all my above selects to include a SUM and got this error (which I expected)

The sum or average aggregate operation cannot take a char data type
as an argument. (22018)

Thank you for you time, sir.

lisa

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 14:43:36

For char types you could use a min/max. Make sure the data thats being returned is what you expect out of it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scared
Starting Member

14 Posts

Posted - 2007-06-13 : 14:52:21

[quote]Originally posted by dinakar

Make sure the data thats being returned is what you expect out of it.

Ain't that the truth.

I went in and included every field I'm reporting in the group by clause (I'm reporting about 100 fields into this flat file) and that seemed to work. Does this put a lot of overhead onto cpu processing? I'd guess it would.

lisa

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 15:00:23

I would think so. SQL Server has to calculate the MIN/MAX for each row. So yes there will be some overhead. If you are using some reporting tool to display the data why not do the group by over there? Move some burden from db server to reporting server?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scared
Starting Member

14 Posts

Posted - 2007-06-13 : 16:56:38

OK.. I went in and changed all the char fields to be prefixed by

min(mystring)

then I added 4 numeric fields to the query (which worked before
I added them) :

+ sum(fit)
+ sum(fica)
+ sum(medicare)
+ sum(fit_earnings)

I got these two errors:

ODBC error 3606 Arithmetic overflow occurred (01000)

and

ODBC Error 8414 Error converting data type varchar to numeric (42000)

Do you thing I could avoid this by taking the MINs off the char fields and grouping by all the fields I need to report to the flat file?

Thanks in advance

Lisa

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 19:43:34

Yes you can do that too.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources