[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

GROUP BY

Posted on 2013-11-05
16
Medium Priority
?
320 Views
Last Modified: 2013-11-12
Hello,
I use below code,
the results I get is few lines per client.

I'd like to have it as one line per client.
please sample attached.
highlighted is what I'd like to have.

any help is appreciated,

Select fo.Accountnumber, (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
st.Description as [Service Type] ,SUM (SubTotalAmount) AS [SubTotalAmount]
FROM finalizedorders fo
LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID LEFT JOIN VehicleTypes vt ON fo.VehicleTypeId = vt.VehicleTypeID
WHERE fo.servicetypeid = st.servicetypeid
and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
Group by st.Description,st.servicetypeid, fo.accountnumber
Order by (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber)

thanks,
Sample.xlsx
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
  • 8
  • 7
16 Comments
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39624006
Hello,

This is a sample query. Could you please check below in meantime I'll work on actual query?

SELECT
	Table_Pivot.Accountnumber, Table_Pivot.AccountCode, Table_Pivot.[5 HOURS],
	Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS]
FROM
(
	SELECT Accountnumber = 1186, AccountCode = '2742022', ServiceType = '5 HOURS', SubTotalAmount = 13.95
	UNION ALL
	SELECT Accountnumber = 1318, AccountCode = '2772666', ServiceType = '2 HOURS', SubTotalAmount = 29.37
	UNION ALL
	SELECT Accountnumber = 1318, AccountCode = '2772666', ServiceType = '.45 MINUTES', SubTotalAmount = 27.02
	UNION ALL
	SELECT Accountnumber = 1504, AccountCode = '2827700  - 1', ServiceType = '2 HOURS', SubTotalAmount = 2.35
	UNION ALL
	SELECT Accountnumber = 1504, AccountCode = '2827700  - 1', ServiceType = '5 HOURS', SubTotalAmount = 4.9
	UNION ALL
	SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '2 HOURS', SubTotalAmount = 109.29
	UNION ALL
	SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '5 HOURS', SubTotalAmount = 54.74
	UNION ALL
	SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = 'OVN EXPRESS', SubTotalAmount = 160.62
	UNION ALL
	SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '.45 MINUTES', SubTotalAmount = 12.27 
) Table1
PIVOT (SUM(SubTotalAmount) FOR ServiceType IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS])) AS Table_Pivot
ORDER BY Table_Pivot.AccountCode

Open in new window



Best Regards,
Mohit Pandit
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39624008
Here's a tutorial on SQL Server GROUP BY Solutions, in case it helps.
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39624009
Please check below code with actual query and let me know in case you find any issue.

SELECT
	Table_Pivot.Accountnumber, Table_Pivot.AccountCode, 
	Table_Pivot.[Service Type],
	Table_Pivot.[5 HOURS], Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS]
FROM
(
	Select 
		fo.Accountnumber, 
		(Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
		st.Description as [Service Type] ,
		SUM (SubTotalAmount) AS [SubTotalAmount]
	FROM finalizedorders fo
	LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID LEFT JOIN VehicleTypes vt ON fo.VehicleTypeId = vt.VehicleTypeID
	WHERE fo.servicetypeid = st.servicetypeid
	and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
	Group by st.Description,st.servicetypeid, fo.accountnumber
) Table1
PIVOT (SUM(SubTotalAmount) FOR ServiceType IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS])) AS Table_Pivot
Order by (Select AccountCode from Clients where Clients.AccountNumber = Table_Pivot.accountnumber)

Open in new window


BR
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:W.E.B
ID: 39624013
Thanks,

tested sample, it looks good,
however,
please note,
I have thousands of accountnumbers?

thanks again,
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39624018
Sure, please note expert (jimhorn) shared a tutorial. Kindly read it for basic fundamentals as it will assist you further.

BR
0
 

Author Comment

by:W.E.B
ID: 39624020
Hello,

I get error
Msg 207, Level 16, State 1, Line 18
Invalid column name 'ServiceType'.

PIVOT (SUM(SubTotalAmount) FOR ServiceType IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS])) AS Table_Pivot

thanks
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39624030
Okay, Service Type should have space as below code:

SELECT
	Table_Pivot.Accountnumber, Table_Pivot.AccountCode, 
	Table_Pivot.[Service Type],
	Table_Pivot.[5 HOURS], Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS]
FROM
(
	Select 
		fo.Accountnumber, 
		(Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
		st.Description as [Service Type] ,
		SUM (SubTotalAmount) AS [SubTotalAmount]
	FROM finalizedorders fo
	LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID LEFT JOIN VehicleTypes vt ON fo.VehicleTypeId = vt.VehicleTypeID
	WHERE fo.servicetypeid = st.servicetypeid
	and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
	Group by st.Description,st.servicetypeid, fo.accountnumber
) Table1
PIVOT (SUM(SubTotalAmount) FOR [Service Type] IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS])) AS Table_Pivot
Order by (Select AccountCode from Clients where Clients.AccountNumber = Table_Pivot.accountnumber)
                                            

Open in new window

0
 

Author Comment

by:W.E.B
ID: 39624052
sorry,
but I still get same error

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Service Type'.

      Table_Pivot.[Service Type],

thanks
0
 
LVL 5

Accepted Solution

by:
MohitPandit earned 1200 total points
ID: 39624076
Oh, I commented Service Type in final output. Please check & let me know.

                                           
SELECT
	Table_Pivot.Accountnumber, Table_Pivot.AccountCode, 
	--Table_Pivot.[Service Type],
	Table_Pivot.[5 HOURS], Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS]
FROM
(
	Select 
		fo.Accountnumber, 
		(Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
		st.Description as [Service Type] ,
		SUM (SubTotalAmount) AS [SubTotalAmount]
	FROM finalizedorders fo
	LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID LEFT JOIN VehicleTypes vt ON fo.VehicleTypeId = vt.VehicleTypeID
	WHERE fo.servicetypeid = st.servicetypeid
	and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
	Group by st.Description,st.servicetypeid, fo.accountnumber
) Table1
PIVOT (SUM(SubTotalAmount) FOR [Service Type] IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS])) AS Table_Pivot
Order by (Select AccountCode from Clients where Clients.AccountNumber = Table_Pivot.accountnumber)                                  

Open in new window

0
 

Author Comment

by:W.E.B
ID: 39624108
sweet,
this works,

question,
I have more services to add,
is there anyway I can add all other services under one service (Other Services)?

very Much appreciated,
thanks
0
 

Author Comment

by:W.E.B
ID: 39624953
Do I need to open a second question for this?

question,
I have more services to add,
is there anyway I can add all other services under one service (Other Services)?

very Much appreciated,
thanks
0
 

Author Closing Comment

by:W.E.B
ID: 39625671
thank you very much for your help.
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39626416
Hello,

No need to open new question, here you go with sample query as I hope that now you are able to change in actual query. Please try once to modify if you are not able then I can provide in actual query.

SELECT
	Table_Pivot.Accountnumber, Table_Pivot.AccountCode, Table_Pivot.[5 HOURS],
	Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS], 
	Table_Pivot.[OTHER SERVICES]
FROM
(
	SELECT
		I_Table1.Accountnumber, I_Table1.AccountCode,
		ServiceType =
			CASE 
				WHEN I_Table1.ServiceType IN('5 HOURS', '2 HOURS', '.45 MINUTES', 'OVN EXPRESS') 
				THEN I_Table1.ServiceType 
				ELSE 'OTHER SERVICES' 
			END,
		I_Table1.SubTotalAmount
	FROM
	(
		SELECT Accountnumber = 1186, AccountCode = '2742022', ServiceType = '5 HOURS', SubTotalAmount = 13.95
		UNION ALL
		SELECT Accountnumber = 1318, AccountCode = '2772666', ServiceType = '2 HOURS', SubTotalAmount = 29.37
		UNION ALL
		SELECT Accountnumber = 1318, AccountCode = '2772666', ServiceType = '.45 MINUTES', SubTotalAmount = 27.02
		UNION ALL
		SELECT Accountnumber = 1504, AccountCode = '2827700  - 1', ServiceType = '2 HOURS', SubTotalAmount = 2.35
		UNION ALL
		SELECT Accountnumber = 1504, AccountCode = '2827700  - 1', ServiceType = '5 HOURS', SubTotalAmount = 4.9
		UNION ALL
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '2 HOURS', SubTotalAmount = 109.29
		UNION ALL
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '5 HOURS', SubTotalAmount = 54.74
		UNION ALL
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = 'OVN EXPRESS', SubTotalAmount = 160.62
		UNION ALL
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '.45 MINUTES', SubTotalAmount = 12.27
		UNION ALL -- Two records added from here
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '1.5 MINUTES', SubTotalAmount = 12.27
		UNION ALL
		SELECT Accountnumber = 1273, AccountCode = '2827700  - 1', ServiceType = '100 MINUTES', SubTotalAmount = 12.27
	) I_Table1 -- Internal Table
) Table1
PIVOT (SUM(SubTotalAmount) FOR ServiceType IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS], [OTHER SERVICES])) AS Table_Pivot
ORDER BY Table_Pivot.AccountCode
                                            

Open in new window


Best Regards,
Mohit Pandit
0
 

Author Comment

by:W.E.B
ID: 39626997
Hi Mohit,
appreciate your follow up and help,

Sample code you sent me is working,
But, I can't seem to get working when I modify it to work on all clients.

thanks again,
0
 

Author Comment

by:W.E.B
ID: 39627011
This is what I changed it to
I get error message

Msg 207, Level 16, State 1, Line 11
Invalid column name 'ServiceType'.

SELECT
      Table_Pivot.Accountnumber, Table_Pivot.AccountCode, Table_Pivot.[5 HOURS],
      Table_Pivot.[2 HOURS], Table_Pivot.[.45 MINUTES], Table_Pivot.[OVN EXPRESS],
      Table_Pivot.[OTHER SERVICES]
FROM
(
      SELECT
            I_Table1.Accountnumber, I_Table1.AccountCode,
            ServiceType =
                  CASE
                        WHEN I_Table1.ServiceType IN ('5 HOURS', '2 HOURS', '.45 MINUTES', 'OVN EXPRESS')
                        THEN I_Table1.ServiceType
                        ELSE 'OTHER SERVICES'
                  END,
            I_Table1.SubTotalAmount
      FROM
(
      Select
            fo.Accountnumber,
            (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
            st.Description as [Service Type] ,
            SUM (SubTotalAmount) AS [SubTotalAmount]
      FROM finalizedorders fo
      LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID
      WHERE fo.servicetypeid = st.servicetypeid
      and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
      Group by st.Description,st.servicetypeid, fo.accountnumber
) I_Table1

) Table1
PIVOT (SUM(SubTotalAmount) FOR ServiceType IN ([5 HOURS],[2 HOURS],[.45 MINUTES],[OVN EXPRESS], [OTHER SERVICES])) AS Table_Pivot
ORDER BY Table_Pivot.AccountCode
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39640979
Hello,

It seems that you have space in Service Type in "I_Table1" derived table i.e "st.Description as [Service Type]" it should be "st.Description as [ServiceType]

Kindly take a look over it and let me know.

BR
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 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