MSSQL Query With SUM Function To Include Other Fields

HELLO...

I have a Table in MSSQL...call it "CustomerPurchases", that has the following fields:

CustomerNumber
FirstName
LastName
PhoneNumber
ProdutSales
ServiceSales

I need to summarize the data...BUT...I *ALSO* need to display...the "other" (non-summarized) fields in the query's output.

I created a query like the following:

Select CustomerNumber, sum(ProductSales), sum(ServiceSales)
from CustomerPurchases
group by CustomerNumber

That works, certainly, but I need to display:
   FirstName
   LastName
   PhoneNumber
...on the SAME OUTPUT.  

When I attempt to add those (3) other fields, I get that "...is invalid in the select list because it is not contained in either an aggregate function..." error.  What is the correct syntax/method to use here?

Please help!...much appreciated!...Mark
datatechcorpAsked:
Who is Participating?
 
awking00Commented:
select CustomerNumber, FirstName, LastName, PhoneNumber,
sum(ProductSales) over (partition by Customer order by CustomerNumber) as sum_prod_sales,
sum(ServiceSales) over (partition by Customer order by CustomerNumber) as sum_serv_sales
from CustomerPurchases;
0
 
Peter KipropCommented:
Try

Select CustomerNumber,FirstName,LastName,PhoneNumber, sum(ProductSales), sum(ServiceSales)
from CustomerPurchases
group by CustomerNumber,FirstName,LastName,PhoneNumber
0
 
IrogSintaCommented:
Try this:
Select CustomerNumber, FirstName, LastName, PhoneNumber, sum(ProductSales), sum(ServiceSales)
from CustomerPurchases
group by CustomerNumber, FirstName, LastName, PhoneNumber

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
datatechcorpAuthor Commented:
Thank you both, IrogSinta, and Pthepebble, for responding.  When I do that, I get a bunch of NULLS fed back, which I know are not true.  Should I be looking to do a querry...and then a subquery with a join, perhaps?  Thoughts?

Thanks!...Mark
0
 
IrogSintaCommented:
Would that be because some of your PhoneNumbers are null for the same customer in this CustomerPurchases table?  If so, you probably should do a join to a Customers table to get the FirstName, LastName, and PhoneNumber fields.  Something like this:

SELECT P.CustomerNumber, C.FirstName, C.LastName, C.PhoneNumber, Sum(P.ProductSales) AS SumOfProductSales, Sum(P.ServiceSales) AS SumOfServiceSales
FROM CustomerPurchases As P INNER JOIN Customers As C ON P.CustomerNumber = C.CustomerNumber
GROUP BY P.CustomerNumber, C.FirstName, C.LastName, C.PhoneNumber

Open in new window

0
 
krtyknmsqlCommented:
Try this and let me know

SELECT CustomerNumber, MAX(FirstName) FirstName, MAX(LastName) LastName, MAX(PhoneNumber) PhoneNumber, Sum(ProductSales) AS SumOfProductSales, Sum(ServiceSales) AS SumOfServiceSales
FROM CustomerPurchases 
GROUP BY CustomerNumber

Open in new window

0
 
awking00Commented:
Sorry for the typo, should be ...(partition by CustomerNumber ...
0
 
datatechcorpAuthor Commented:
Thank you all SOOOOOO much!  I just *knew* it was (freakin' MSSQL) syntax!  All (3) solutions worked, so I'm being fair and awarding the points *asap* to y'all.  Again, very much appreciated!...Mark

P.S...EE is forcing me to choose a "best" solution...but, honestly, all (3) worked great...so please, take no offense anyone...I'm truly grateful !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.