Aggregate Function / Group By

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Aggregate Function / Group By

Aggregate Function / Group By

Rate Topic

Display Mode

Topic Options

Author

Message

SQL.Chick

Posted Tuesday, March 03, 2009 3:30 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 06, 2011 12:48 PM
Points: 11, Visits: 41

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

Post #667707

Lowell

Posted Tuesday, March 03, 2009 3:44 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 11,791, Visits: 28,075

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #667717

Jeff Moden

Posted Tuesday, March 03, 2009 8:02 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

and PYRLRTYP = '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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #667800

SQL.Chick

Posted Wednesday, March 04, 2009 7:57 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 06, 2011 12:48 PM
Points: 11, Visits: 41

The PYRLRTYP is on the UPR30300 table. Thank You !!! :)

Post #668255

« Prev Topic | Next Topic »

Permissions