SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 COUNT AND SUM		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KinRookie
Starting Member

1 Posts
Posted - 12/10/2012 :  04:02:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 12/10/2012 :  04:41:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 12/14/2012 :  02:14:00  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000