SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use the subquery column highglighted in the GROUP BY


How to use the subquery column highglighted in the GROUP BY

Author
Message
eobiki10
eobiki10
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 369
Hi All,

I need to know how to use the subquery column (billings_to_date)in the GROUP BY as it is showing up as error.


SELECT p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name
,o.offc_desc
,'Insolvency' AS matter_code
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name) AS Bank
,CASE WHEN m.matter_code NOT IN ('IBR','ISR','BNK','ADV')
THEN ISNULL(mins.original_estimated_fee,0) + ISNULL(mins.original_contingent_fee,0)
ELSE 0
END AS [Original Fee]
,isnull((Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'),'N') AS [Walk In],em.employee_name,
[color=#8ab74e]isnull((SELECT sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO), 0) AS billings_to_date[/color]
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno
INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof,2) IN (SELECT FieldName FROM dbo._000007_SplitList(@Profit_Center,',' ))
INNER JOIN CTE_Mins AS mins ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'

WHERE ....................(Intentionally removed to reduce code)

GROUP BY p.period
,m.clnt_matt_code
,m.matter_name
,o.offc_desc
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
,m.matter_code
,mins.original_estimated_fee
,mins.original_contingent_fee, i.matter_uno ,em.employee_name
eobiki10
eobiki10
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 369
Please my intention is to show the code that I have problem with in a different colour but it didnt work. so please ignore the colour code.

I want to know how to use this code in a GROUP BY clause



isnull((SELECT sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO), 0) AS billings_to_date
timwell
timwell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 3218
(Without going into the details of your query) maybe you can convert your query to a CTE then group the result?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64480 Visits: 17976
Using a subselect as a derived column is performance time bomb. You have at least a couple of those in there already. You also have a split function in there which raises at least a yellow flag. Does that split function use xml or looping?

Not quite sure why you have the second subselect in there. The one selecting from BLT_BILLM with the huge sum. You already have that table in your query.

See if something like this gets you closer.


SELECT p.period,
m.clnt_matt_code AS [Client Code],
m.matter_name,
o.offc_desc,
'Insolvency' AS matter_code,
Isnull(l.Merged_Source_of_Work_Lender_Desc, k.name) AS Bank,
CASE
WHEN m.matter_code NOT IN ( 'IBR', 'ISR', 'BNK', 'ADV' ) THEN Isnull(mins.original_estimated_fee, 0)
+ Isnull(mins.original_contingent_fee, 0)
ELSE 0
END AS [Original Fee],
Isnull(ii.WalkIn, 'N') AS [Walk In],
em.employee_name,
Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0) AS billings_to_date
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno
INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof, 2) IN (SELECT FieldName
FROM dbo._000007_splitlist(@Profit_Center, ','))
INNER JOIN CTE_Mins AS mins
ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p
ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'
left join (SELECT TOP 1 'Y' as WalkIn FROM bo_live5.dbo.hba_source_bus) ii on ii.matter_uno = i.matter_uno AND ii.comments LIKE '%walk-in%'
GROUP BY p.period
,m.clnt_matt_code
,m.matter_name
,o.offc_desc
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
,m.matter_code
,mins.original_estimated_fee
,mins.original_contingent_fee, i.matter_uno ,em.employee_name
, Isnull(ii.WalkIn, 'N')
, Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
eobiki10
eobiki10
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 369
Thanks SSC veteran and SSC Champion for your suggestions and also performance tips. However Sean, I would like to know the format that you used to get the SQL to be so readable in this forum. I mean mine always get messed up anytime I pasted the sql from my computer to this forum or is the format only reserved for veterans and champions?

EO
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64480 Visits: 17976
eobiki10 (12/20/2013)
Thanks SSC veteran and SSC Champion for your suggestions and also performance tips. However Sean, I would like to know the format that you used to get the SQL to be so readable in this forum. I mean mine always get messed up anytime I pasted the sql from my computer to this forum or is the format only reserved for veterans and champions?

EO


Not reserved at all. When you are posting you will notice IFCode shortcut on the left. Put your code and such inside those and it will keep things nice and tidy for you.

If you quote my previous post you can view them in action if that helps too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
eobiki10
eobiki10
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 369
Sean,

I tried your suggestion and I have this error message. What should I do?

Msg 144, Level 15, State 1, Procedure sp_Bank_Work_Report1, Line 18
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42677 Visits: 20015
Is bo_Live5.dbo.BLT_BILLM the only table in your FROMlist which has sets of rows having the same value of MATTER_UNO/matter_name?
Are you expecting, in your output, only one row per matter_name?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
eobiki10
eobiki10
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 369
Yes, I am expecting one row for matter name.

I just have a workaround on it and that is to remove the sum in the grouping in the code and now do the grouping in SSRS.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42677 Visits: 20015
Have you tried aggregating the bills in isolation from the rest of the query? Here's one way which looks clean and performs quite well:

SELECT 
p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name
,o.offc_desc
,'Insolvency' AS matter_code
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name) AS Bank

,CASE WHEN m.matter_code NOT IN ('IBR','ISR','BNK','ADV')
THEN ISNULL(mins.original_estimated_fee,0) + ISNULL(mins.original_contingent_fee,0)
ELSE 0
END AS [Original Fee]

,isnull((
Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'
),'N') AS [Walk In],

em.employee_name,

isnull(x.billings_to_date, 0) AS billings_to_date
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno

--INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
CROSS APPLY (
SELECT billings_to_date = sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO
) x

INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof,2) IN (SELECT FieldName FROM dbo._000007_SplitList(@Profit_Center,',' ))
INNER JOIN CTE_Mins AS mins ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'

WHERE ....................(Intentionally removed to reduce code)

--GROUP BY p.period
--,m.clnt_matt_code
--,m.matter_name
--,o.offc_desc
--,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
--,m.matter_code
--,mins.original_estimated_fee
--,mins.original_contingent_fee, i.matter_uno ,em.employee_name



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search