W.E.B
asked on
GROUP BY
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.servicet ypeid, fo.accountnumber
Order by (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber)
thanks,
Sample.xlsx
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.servicet
Order by (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber)
thanks,
Sample.xlsx
Here's a tutorial on SQL Server GROUP BY Solutions, in case it helps.
Please check below code with actual query and let me know in case you find any issue.
BR
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)
BR
ASKER
Thanks,
tested sample, it looks good,
however,
please note,
I have thousands of accountnumbers?
thanks again,
tested sample, it looks good,
however,
please note,
I have thousands of accountnumbers?
thanks again,
Sure, please note expert (jimhorn) shared a tutorial. Kindly read it for basic fundamentals as it will assist you further.
BR
BR
ASKER
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
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
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)
ASKER
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
but I still get same error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Service Type'.
Table_Pivot.[Service Type],
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
ASKER
thank you very much for your help.
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.
Best Regards,
Mohit Pandit
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
Best Regards,
Mohit Pandit
ASKER
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,
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,
ASKER
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.servicet ypeid, 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
I get error message
Msg 207, Level 16, State 1, Line 11
Invalid column name 'ServiceType'.
SELECT
Table_Pivot.Accountnumber,
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.servicet
) 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
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
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
This is a sample query. Could you please check below in meantime I'll work on actual query?
Open in new window
Best Regards,
Mohit Pandit