GROUP BY Subcase_Number within several columns

Question

GROUP BY Subcase_Number within several columns

SqlHomebrewer

SSC Eights!

Points: 874
More actions
April 3, 2013 at 11:39 am

#292041

I posted on forums.oracle a couple hours ago but haven't heard anything yet. Figured I'd try this board as well.
I have a query that I pulled from a Hyperion report and it is going to be used to create a report in SSRS. The query that I exported from the report does not include how the data was massaged in the report. There are several columns and two of them are SUBCASE_NUMBER and DURATION. The report sums the duration for each subcase number. In some cases, there is only one subcase number with one duration time, in other cases there are several of the same subcase numbers with varying duration times. When I run the following query, I get the results I want.
SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION
FROM SUBCASE AL21, ONSITE_TIME_LOG AL31
WHERE AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE (+)
AND ((AL21.SUBCASE_NUMBER LIKE '2049356%'
OR AL21.SUBCASE_NUMBER LIKE '2049462%'
OR AL21.SUBCASE_NUMBER LIKE '2057852%'
OR AL21.SUBCASE_NUMBER LIKE '2057877%'
OR AL21.SUBCASE_NUMBER LIKE '2057897%'
OR AL21.SUBCASE_NUMBER LIKE '2057930%'
OR AL21.SUBCASE_NUMBER LIKE '2057948%'
OR AL21.SUBCASE_NUMBER LIKE '2057963%'
OR AL21.SUBCASE_NUMBER LIKE '2057984%'
OR AL21.SUBCASE_NUMBER LIKE '2057996%'
OR AL21.SUBCASE_NUMBER LIKE '2058015%'
OR AL21.SUBCASE_NUMBER LIKE '2058032%'
OR AL21.SUBCASE_NUMBER LIKE '2058049%'
OR AL21.SUBCASE_NUMBER LIKE '2066771%')
OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'))
GROUP BY AL21.SUBCASE_NUMBER
ORDER BY AL21.SUBCASE_NUMBER
When I try to combine that query to the original query to include the other columns, I'm not entirely sure how to go about it. I am getting 'no rows selected'. Could someone tell me where I'm going wrong? (All of those OR subcase title like '% %' were copied exactly from the Hyperion report query. I realize that the CITS Audits are redundant.)
SELECT AL2.REGION_GFS, AL2.DIVISION_GFS, AL2.GFS_SITE_TYPE, AL1.CASE_TYPE_GROUP, AL1.X_CASE_TYPE_LEVEL1, AL1.X_CASE_TYPE_LEVEL2,
AL1.X_CASE_TYPE_LEVEL3, AL2.SUBCASE_NUMBER, trunc((AL2.SUBCASE_CREATION_TIME)), trunc((AL2.SUBCASE_FIRST_CLOSE_DATE)),
AL2.FIRST_ON_SCHEDULE_DT, AL3.DURATION, AL3.START_TIME, AL3.OWNER_FIRST_NAME, AL3.OWNER_LAST_NAME, AL3.OWNER_WORK_GROUP,
AL2.SUBCASE_STATUS, AL3.TIME_TYPE, AL1.CASE_NUMBER, AL2.SUBCASE_CONDITION, AL1.CASE_OWNER_FIRST_NAME, AL1.CASE_OWNER_LAST_NAME,
AL1.CASE_OWNER_WORK_GRP, AL2.SUBCASE_TITLE
FROM CASE AL1, SUBCASE AL2, ONSITE_TIME_LOG AL3,
(SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION
FROM SUBCASE AL21, ONSITE_TIME_LOG AL31
WHERE AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE (+)
AND ((AL21.SUBCASE_NUMBER LIKE '2049356%'
OR AL21.SUBCASE_NUMBER LIKE '2049462%'
OR AL21.SUBCASE_NUMBER LIKE '2057852%'
OR AL21.SUBCASE_NUMBER LIKE '2057877%'
OR AL21.SUBCASE_NUMBER LIKE '2057897%'
OR AL21.SUBCASE_NUMBER LIKE '2057930%'
OR AL21.SUBCASE_NUMBER LIKE '2057948%'
OR AL21.SUBCASE_NUMBER LIKE '2057963%'
OR AL21.SUBCASE_NUMBER LIKE '2057984%'
OR AL21.SUBCASE_NUMBER LIKE '2057996%'
OR AL21.SUBCASE_NUMBER LIKE '2058015%'
OR AL21.SUBCASE_NUMBER LIKE '2058032%'
OR AL21.SUBCASE_NUMBER LIKE '2058049%'
OR AL21.SUBCASE_NUMBER LIKE '2066771%')
OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'))
GROUP BY AL21.SUBCASE_NUMBER
ORDER BY AL21.SUBCASE_NUMBER) GROUPED
WHERE ((AL2.SUBCASE_OBJID = AL3.SUBC_ONSITE2SUBCASE (+)
AND AL1.CASE_OBJID=AL2.SUBCASE2CASE)
AND (GROUPED.SUBCASE_NUMBER = AL2.SUBCASE_NUMBER
AND GROUPED.TOTAL_DURATION = AL3.DURATION))
AND (((AL2.SUBCASE_NUMBER LIKE '2049356%'
OR AL2.SUBCASE_NUMBER LIKE '2049462%'
OR AL2.SUBCASE_NUMBER LIKE '2057852%'
OR AL2.SUBCASE_NUMBER LIKE '2057877%'
OR AL2.SUBCASE_NUMBER LIKE '2057897%'
OR AL2.SUBCASE_NUMBER LIKE '2057930%'
OR AL2.SUBCASE_NUMBER LIKE '2057948%'
OR AL2.SUBCASE_NUMBER LIKE '2057963%'
OR AL2.SUBCASE_NUMBER LIKE '2057984%'
OR AL2.SUBCASE_NUMBER LIKE '2057996%'
OR AL2.SUBCASE_NUMBER LIKE '2058015%'
OR AL2.SUBCASE_NUMBER LIKE '2058032%'
OR AL2.SUBCASE_NUMBER LIKE '2058049%'
OR AL2.SUBCASE_NUMBER LIKE '2066771%')
OR (AL2.SUBCASE_TITLE LIKE '%CITS Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDIT%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDITS%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITS-Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS-Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAUDIT%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAudit%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAUDITS%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAudits%')))
"Nicholas"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Given the lack of details it is very difficult to offer much assistance here. I don't really understand why you have the subquery at all. I would also recommend that use the newer join styles instead of the old styles. It is much easier to read and less likely to produce accidental cartesian products. Additionally I would suggest to use meaningful aliases instead of just numbering them.

This query should produce the same thing as the first query you posted. It is however a lot shorter and a lot easier to read.

SELECT sc.SUBCASE_NUMBER, SUM(otl.DURATION/60) AS TOTAL_DURATION

FROM SUBCASE sc

JOIN ONSITE_TIME_LOG otl on sc.SUBCASE_OBJID = otl.SUBC_ONSITE2SUBCASE

WHERE

LEFT(sc.SUBCASE_NUMBER, 7) IN

(

'2049356',

'2049462',

'2057852',

'2057877',

'2057897',

'2057930',

'2057948',

'2057963',

'2057984',

'2057996',

'2058015',

'2058032',

'2058049',

'2066771'

)

OR sc.SUBCASE_TITLE LIKE '%CITS%Audit%'

GROUP BY sc.SUBCASE_NUMBER

ORDER BY sc.SUBCASE_NUMBER

Now I realize I haven't actually helped with your issue. Are you doing this in Oracle or SQL?

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jo Pattyn SSC-Dedicated Points: 32064 More actions · Answer 2

Hello Nicolas,

Where does the condition GROUPED.TOTAL_DURATION = AL3.DURATION come from?

You could also try to use a "WITH clause"

WITH mysubquery AS

(SELECT ...)

SELECT EXTRA.*

FROM EXTRA

INNER JOIN mysubquery on extra.X=mysubquery.Y

My attempt to an rewritten query leads to (replacing ... = (+) with left join)

SELECT AL2.REGION_GFS, AL2.DIVISION_GFS, AL2.GFS_SITE_TYPE, AL1.CASE_TYPE_GROUP, AL1.X_CASE_TYPE_LEVEL1, AL1.X_CASE_TYPE_LEVEL2,

AL1.X_CASE_TYPE_LEVEL3, AL2.SUBCASE_NUMBER, trunc((AL2.SUBCASE_CREATION_TIME)), trunc((AL2.SUBCASE_FIRST_CLOSE_DATE)),

AL2.FIRST_ON_SCHEDULE_DT, AL3.DURATION, AL3.START_TIME, AL3.OWNER_FIRST_NAME, AL3.OWNER_LAST_NAME, AL3.OWNER_WORK_GROUP,

AL2.SUBCASE_STATUS, AL3.TIME_TYPE, AL1.CASE_NUMBER, AL2.SUBCASE_CONDITION, AL1.CASE_OWNER_FIRST_NAME, AL1.CASE_OWNER_LAST_NAME,

AL1.CASE_OWNER_WORK_GRP, AL2.SUBCASE_TITLE

FROM CASE AL1

INNER JOIN SUBCASE AL2 ON AL1.CASE_OBJID=AL2.SUBCASE2CASE

LEFT JOIN ONSITE_TIME_LOG AL3 ON AL2.SUBCASE_OBJID = AL3.SUBC_ONSITE2SUBCASE

INNER JOIN

(SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION

FROM SUBCASE AL21

LEFT JOIN ONSITE_TIME_LOG AL31

ON AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE

WHERE

(

(AL21.SUBCASE_NUMBER LIKE '2049356%'

OR AL21.SUBCASE_NUMBER LIKE '2049462%'

OR AL21.SUBCASE_NUMBER LIKE '2057852%'

OR AL21.SUBCASE_NUMBER LIKE '2057877%'

OR AL21.SUBCASE_NUMBER LIKE '2057897%'

OR AL21.SUBCASE_NUMBER LIKE '2057930%'

OR AL21.SUBCASE_NUMBER LIKE '2057948%'

OR AL21.SUBCASE_NUMBER LIKE '2057963%'

OR AL21.SUBCASE_NUMBER LIKE '2057984%'

OR AL21.SUBCASE_NUMBER LIKE '2057996%'

OR AL21.SUBCASE_NUMBER LIKE '2058015%'

OR AL21.SUBCASE_NUMBER LIKE '2058032%'

OR AL21.SUBCASE_NUMBER LIKE '2058049%'

OR AL21.SUBCASE_NUMBER LIKE '2066771%'

)

OR

(

AL21.SUBCASE_TITLE LIKE '%CITS Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'

)

GROUP BY AL21.SUBCASE_NUMBER

ORDER BY AL21.SUBCASE_NUMBER) GROUPED

ON GROUPED.SUBCASE_NUMBER = AL2.SUBCASE_NUMBER

AND GROUPED.TOTAL_DURATION = AL3.DURATION

SqlHomebrewer SSC Eights! Points: 874 More actions · Answer 3

As for the ANSI joins, when I tried switching to ANSI some of the rows were left out. With the old outer joins, the output was 243 rows. With the ANSI joins, the output was 239 rows. That's why I left those annoying outer joins in the query. I have roughly 8 months of sql experience, so I'm definitely still wearing my training wheels. I wasn't thinking this (GROUPED.TOTAL_DURATION = AL3.DURATION) all the way through, so bye-bye Duration.

Jo, I like the way you have the query coded and I will try that out right away.

"Nicholas"

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

5280_Lifestyle (4/3/2013)
As for the ANSI joins, when I tried switching to ANSI some of the rows were left out. With the old outer joins, the output was 243 rows. With the ANSI joins, the output was 239 rows. That's why I left those annoying outer joins in the query. I have roughly 8 months of sql experience, so I'm definitely still wearing my training wheels. I wasn't thinking this (GROUPED.TOTAL_DURATION = AL3.DURATION) all the way through, so bye-bye Duration.
Jo, I like the way you have the query coded and I will try that out right away.

No worries about training wheels. My guess is that when you used correct joins you accidentally fat fingered something. Also as for the two "groups" of OR sections there is no point. They are all OR so there is no need to use the extra conditions.

WHERE

(

(AL21.SUBCASE_NUMBER LIKE '2049356%'

OR AL21.SUBCASE_NUMBER LIKE '2049462%'

OR AL21.SUBCASE_NUMBER LIKE '2057852%'

OR AL21.SUBCASE_NUMBER LIKE '2057877%'

OR AL21.SUBCASE_NUMBER LIKE '2057897%'

OR AL21.SUBCASE_NUMBER LIKE '2057930%'

OR AL21.SUBCASE_NUMBER LIKE '2057948%'

OR AL21.SUBCASE_NUMBER LIKE '2057963%'

OR AL21.SUBCASE_NUMBER LIKE '2057984%'

OR AL21.SUBCASE_NUMBER LIKE '2057996%'

OR AL21.SUBCASE_NUMBER LIKE '2058015%'

OR AL21.SUBCASE_NUMBER LIKE '2058032%'

OR AL21.SUBCASE_NUMBER LIKE '2058049%'

OR AL21.SUBCASE_NUMBER LIKE '2066771%'

)

OR

(

AL21.SUBCASE_TITLE LIKE '%CITS Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'

)

The extra parenthesis make this more confusing trying to figure out the differences. I would much prefer the one I posted originally. It is the same thing but a ton easier on the eyes. 😛

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

5280_Lifestyle (4/3/2013)
As for the ANSI joins, when I tried switching to ANSI some of the rows were left out. With the old outer joins, the output was 243 rows. With the ANSI joins, the output was 239 rows. That's why I left those annoying outer joins in the query. I have roughly 8 months of sql experience, so I'm definitely still wearing my training wheels. I wasn't thinking this (GROUPED.TOTAL_DURATION = AL3.DURATION) all the way through, so bye-bye Duration.
Jo, I like the way you have the query coded and I will try that out right away.

Going back to the original query, when converting an ANSI-89 style outer join to an ANSI-92 style outer join some of what you think may be filter criteria and need to stay in the WHERE clause is actually part of the join criteria.

Based on this, this may be your original query rewritten as an ANSI-92 style join.

SELECT

AL21.SUBCASE_NUMBER,

SUM(AL31.DURATION/60) TOTAL_DURATION

FROM

SUBCASE AL21

LEFT OUTER JOIN ONSITE_TIME_LOG AL31

ON (AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE

AND ((AL21.SUBCASE_NUMBER LIKE '2049356%'

OR AL21.SUBCASE_NUMBER LIKE '2049462%'

OR AL21.SUBCASE_NUMBER LIKE '2057852%'

OR AL21.SUBCASE_NUMBER LIKE '2057877%'

OR AL21.SUBCASE_NUMBER LIKE '2057897%'

OR AL21.SUBCASE_NUMBER LIKE '2057930%'

OR AL21.SUBCASE_NUMBER LIKE '2057948%'

OR AL21.SUBCASE_NUMBER LIKE '2057963%'

OR AL21.SUBCASE_NUMBER LIKE '2057984%'

OR AL21.SUBCASE_NUMBER LIKE '2057996%'

OR AL21.SUBCASE_NUMBER LIKE '2058015%'

OR AL21.SUBCASE_NUMBER LIKE '2058032%'

OR AL21.SUBCASE_NUMBER LIKE '2058049%'

OR AL21.SUBCASE_NUMBER LIKE '2066771%')

OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'

OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'

OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%')))

GROUP BY

AL21.SUBCASE_NUMBER

ORDER BY

AL21.SUBCASE_NUMBER

Test it and see if it returns the same result set as the original query.

SqlHomebrewer SSC Eights! Points: 874 More actions · Answer 6

Lynn,

I tried using that query and I canceled the query after waiting for about 8 minutes. It didn't like that version.

"Nicholas"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

5280_Lifestyle (4/3/2013)
Lynn,
I tried using that query and I canceled the query after waiting for about 8 minutes. It didn't like that version.

It was worth a shot, but what I talked about is still valid. In the ANSI-89 style joins (everything in the HWERE clause) some of what you may think of as filter conditions, actually needs to move from the WHERE clause to the ON clause when converting to the ANSI-92 style outer joins.

I'd suggest working with a smaller test set of data if you can and go from there when converting from ANSI-89 to ANSI-92 style outer joins.