Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2008 GROUP BY

Posted on 2013-11-05
3
Medium Priority
?
286 Views
Last Modified: 2013-11-05
Hello,
I'm trying to use below code to get some totals per client.
results I'm getting is about 10-15 lines per client.
I need to get only one line per client

SELECT fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,
(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID in (122,123,124)) AS [OVN],
(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID NOT in (122,123,124)) AS [Local],
SUM(fo.SubTotalAmount) AS [SubTotalAamount],
SUM(fo.TotalAmount) AS [TotalAamount]
FROM  FinalizedOrders fo JOIN Clients cl ON fo.AccountNumber = cl.AccountNumber
WHERE fo.OrderDate BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,fo.ServiceTypeID
order BY fo.AccountNumber

Any help is appreciated,
Thanks,
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
MohitPandit earned 1200 total points
ID: 39623903
Hello,

Can you check with below code?

SELECT
	Table1.Name, [OVN] = SUM(Table1.[OVN]), [Local] = SUM(Table1.[Local]),
	[SubTotalAamount] = SUM(Table1.[SubTotalAamount]), [TotalAamount] = SUM(Table1.[TotalAamount])
FROM
(
	SELECT fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,
	(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID in (122,123,124)) AS [OVN],
	(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID NOT in (122,123,124)) AS [Local],
	SUM(fo.SubTotalAmount) AS [SubTotalAamount],
	SUM(fo.TotalAmount) AS [TotalAamount]
	FROM  FinalizedOrders fo JOIN Clients cl ON fo.AccountNumber = cl.AccountNumber
	WHERE fo.OrderDate BETWEEN '2013-01-01' AND '2013-12-31'
	GROUP BY fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,fo.ServiceTypeID
) Table1
GROUP BY Table1.Name
ORDER BY Table1.AccountNumber

Open in new window


Best Regards,
Mohit Pandit
0
 

Author Comment

by:W.E.B
ID: 39623924
Awesome.

I will have a second similar question shortly.

thanks again.
0
 

Author Closing Comment

by:W.E.B
ID: 39623927
Thank you
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question