GROUP AND SUM - SQL Server Forums

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)

GROUP AND SUM

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-02-20 : 10:22:18

Ok, here is the SQL. What it does it display all the PCODE fields but there are duplicates. I want the duplicates to become one and the SUMOBJ to become a tally for all objects for that PCODE.

SELECT VSHPITMS.PCODE, VSHPITMS.OBJECTS,
SUM(VSHPITMS.OBJECTS) AS SUMOBJ, PNAME
FROM EXPORTDOC.DBO.VPROD INNER JOIN
VSHPITMS ON VSHPITMS.PCODE = VPROD.PCODE
WHERE VSHPITMS.SHIPDATE >= '01/03/2002' AND
VSHPITMS.SHIPDATE <= '01/04/2002' AND
VSHPITMS.SHIPNUM LIKE '%[a-z]%' AND
VSHPITMS.OBJECTS IS NOT NULL
GROUP BY VSHPITMS.PCODE, VPROD.PNAME,
VSHPITMS.OBJECTS
ORDER BY VSHPITMS.PCODE

Thanks in advance.

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 10:36:51

SELECT distinct VSHPITMS.PCODE, VSHPITMS.OBJECTS,
, PNAME,Sumobj
FROM EXPORTDOC.DBO.VPROD D INNER JOIN
VSHPITMS ON VSHPITMS.PCODE = d.PCODE
inner join (
select Pcode,
SUM(VSHPITMS.OBJECTS) AS SUMOBJ from EXPORTDOC.DBO.VPROD
group by pcode ) V
ON VSHITMS.PCODE=V.PCODE
WHERE VSHPITMS.SHIPDATE >= '01/03/2002' AND
VSHPITMS.SHIPDATE <= '01/04/2002' AND
VSHPITMS.SHIPNUM LIKE '%[a-z]%' AND
VSHPITMS.OBJECTS IS NOT NULL
ORDER BY VSHPITMS.PCODE

HTH

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources