Click here to monitor SSC
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 Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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 Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 Visits: 2893
(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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
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 Moden's 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 Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
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 Moden's 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 Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
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 Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
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%'
      Wink,'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