Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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
0
datatechcorp
Asked:
datatechcorp
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now