• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

SQL 2008 GROUP BY

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
W.E.B
Asked:
W.E.B
  • 2
1 Solution
 
MohitPanditCommented:
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
 
W.E.BAuthor Commented:
Awesome.

I will have a second similar question shortly.

thanks again.
0
 
W.E.BAuthor Commented:
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.

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