Thread: Grouping by reference and a group within the reference

1. Registered User
Join Date
Oct 2010
Posts
49

Unanswered: Grouping by reference and a group within the reference

Hi,

I have a simple script which sums the values of items and then groups the output by the reference.

Code:
```select reference,sum(Number) from mytable
group by reference```
So with he example table below, I'd like to group by Reference, but create a sub group within the reference of (group anything where the first two characters of the reference begin 16

i.e

Reference, Number
cat,2
dog,1
horse,3
cat,6
dog,3
165,4
162,3
169,6

My results would be

cat,8
dog,4
horse,3
16,13

Is this possible?

2. Registered User
Join Date
Mar 2007
Posts
629
Hi,

simply construct an expression which returns the value expected in the result set and GROUP BY it instead of REFERENCE column.

Based on your specification, it could be this:
Code:
```CASE
WHEN SUBSTR(reference,1,2) = '16' THEN '16'
ELSE reference
END```
If the requirements are different, just change this expression according to them.

3. Registered User
Join Date
Oct 2010
Posts
49
SELECT REFERENCE, CASE
WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
ELSE REFERENCE
END,
sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) FROM MYTABLE
WHERE cl3accountnum IN ('A1','A2','A3')
GROUP BY REFERENCE
HAVING sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) <> 0
ORDER BY 1

This is what I now have, but how do i reference the case in my group?
Ok, I have worked it out, thx for you're help.

SELECT CASE
WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
ELSE REFERENCE
END,
sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) FROM MYTABLE
WHERE cl3accountnum IN ('A1','A2','A3')
GROUP BY CASE
WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
ELSE REFERENCE
END
HAVING sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) <> 0
ORDER BY 1
Last edited by Ziggadebo; 06-27-11 at 07:04.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•