Aggregate Function / Group By

Question

Aggregate Function / Group By

SQL.Chick

SSC Veteran

Points: 267
More actions
March 3, 2009 at 3:30 pm

#218847

Oh SQL guru's what have i done wrong ?
SELECT DISTINCT a.EMPLOYID, a.LASTNAME, a.FRSTNAME, a.SOCSCNUM, a.BRTHDATE, a.GENDER,
a.LOCATNID, a.STRTDATE, a.EMPLOYMENTTYPE, a.LASTDAYWORKED_I, b.PAYRTAMT,
c.CHEKDATE, c.UNTSTOPY, c.UPRTRXAM, d.ZIPCODE,
SUM (c.UPRTRXAM) as Gross_Pay
FROM UPR00100 as a
INNER JOIN UPR00400 as b
ON a.EMPLOYID = b.EMPLOYID
INNER JOIN UPR30300 as c
ON a.EMPLOYID = c.EMPLOYID
and PYRLRTYP = '1'
INNER JOIN UPR00102 as d
ON a.EMPLOYID = d.EMPLOYID
where UNTSTOPY != '0'
GROUP BY a.EMPLOYID,a.LASTNAME

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

if you use GROUP BY, then every column that is not using a SUM() or AVG() or other grouping operation MUST be in the GROUP BY Statement...so your query would need this grouping:

GROUP BY a.EMPLOYID,

a.LASTNAME ,

a.FRSTNAME,

a.SOCSCNUM,

a.BRTHDATE,

a.GENDER,

a.LOCATNID,

a.STRTDATE,

a.EMPLOYMENTTYPE,

a.LASTDAYWORKED_I,

b.PAYRTAMT,

c.CHEKDATE,

c.UNTSTOPY,

c.UPRTRXAM,

d.ZIPCODE,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

I can show you a little performance trick (big one, actually) but I need to know which table the following column is in...

and [font="Arial Black"]PYRLRTYP [/font]= '1'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL.Chick SSC Veteran Points: 267 More actions · Answer 3

The PYRLRTYP is on the UPR30300 table. Thank You !!! 🙂