COUNT 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

General SQL Server Forums

New to SQL Server Programming

COUNT AND SUM

Author

Topic

KinRookie
Starting Member

1 Post

Posted - 2012-12-10 : 04:02:36

Hello all,
i am try to group two records into one by using count distinct and sum.

There is a table "INV" with columns
"INV_NO", "INV_PRO", "INV_QTY", "INV_AMT", "INV_DATE", "INV_CUS"

And there is a customer brought 5 products and it breaks into two invoice "INV10122012001", "INV10122012002", and it shows like this:

INV_NO INV_PRO INV_QTY INV_AMT INV_DATE INV_CUS
INV10122012001 A 3 20 10/12/2012 JACK
INV10122012002 B 2 10 10/12/2012 JACK

All i want to group like this by using count and sum:
INV_NO INV_QTY INV_AMT INV_DATE INV_CUS
2 5 30 10/12/2012 JACK

THANKS

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-10 : 04:41:34

SELECT COUNT(distinct INV_NO) INV_NO, SUM(INV_QTY) INV_QTY, MAX(INV_DATE) INV_DAE, INV_CUS
FROM INV
GROUP BY INV_CUS

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:14:00

if its a invoice level table no need of COUNT(DISTINCT..) as it should have only one record per invoice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources