Home » SQL & PL/SQL » SQL & PL/SQL » Grouping Data (11g Release 2 Windows)
| Grouping Data [message #596279] |
Thu, 19 September 2013 15:18  |
Duane
Messages: 557
Registered: December 2002
|
Senior Member |
|
|
I'm just trying to see if anyone can come up with a way to do some grouping of data and produce the results I need.
I'm trying to get a count of each number by the column.
Value 1 has a count in Column A of 2, Column B of 1, Column C of 3, Column D of 4 and Column E of 1.
Value 2 has a count in Column A of 3, Column B of 1, Column C of 2, Column D of 1 and Column E of 1.
Value 3 has a count in Column A of 1, Column B of 1, Column C of 1, Column D of 2 and Column E of 2.
and so on for 6 numbers.
DATA
CREATE TABLE TEMP_COUNTS
(
A NUMBER,
B NUMBER,
C NUMBER,
D NUMBER,
E NUMBER
)
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(1, 0, 1, 2, 4);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(1, 1, 1, 1, 1);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(2, 2, 2, 2, 4);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(3, 5, 6, 2, 3);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(6, 4, 4, 3, 2);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(4, 3, 3, 2, 5);
Insert into TEMP_COUNTS
(A, B, C, D, E)
Values
(3, 5, 3, 5, 5);
COMMIT;
DESIRED OUTPUT (NOTE: Not actual counts but the result set should look like this)
Value CountA CountB CountC CountD CountE
1 4 2 3 4 3
2 3 1 4 2 2
3 1 1 3 2 2
4 0 0 1 3 4
5 2 2 2 1 1
6 0 3 0 0 2
|
|
|
|
| Re: Grouping Data [message #596284 is a reply to message #596279] |
Thu, 19 September 2013 16:14  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Hello,
Assuming that we're looking for the sequence of values between 1 and 6, then I think a combination of count and decode fulfils your requirement.
SELECT * FROM temp_counts
ORDER BY A, B, C, D, E;
WITH searchedValues AS
(
SELECT level colval
FROM "PUBLIC".dual
CONNECT BY level <= 6
)
SELECT t1.colval,
count(decode(t2.A, t1.colval, t1.colval)) cnt_a,
count(decode(t2.B, t1.colval, t1.colval)) cnt_b,
count(decode(t2.C, t1.colval, t1.colval)) cnt_c,
count(decode(t2.D, t1.colval, t1.colval)) cnt_d,
count(decode(t2.E, t1.colval, t1.colval)) cnt_e
FROM searchedValues t1 CROSS JOIN temp_counts t2
GROUP BY t1.colval
ORDER BY t1.colval;
And I think the output of the second query is what you're looking for (comparing to the SELECT result on your table)
A B C D E
---------- ---------- ---------- ---------- ----------
1 0 1 2 4
1 1 1 1 1
2 2 2 2 4
3 5 3 5 5
3 5 6 2 3
4 3 3 2 5
6 4 4 3 2
7 rows selected.
COLVAL CNT_A CNT_B CNT_C CNT_D CNT_E
---------- ---------- ---------- ---------- ---------- ----------
1 2 1 2 1 1
2 1 1 1 4 1
3 2 1 2 1 1
4 1 1 1 0 2
5 0 2 0 1 2
6 1 0 1 0 0
6 rows selected.
Regards,
Dariyoosh
[Updated on: Thu, 19 September 2013 16:17] Report message to a moderator |
|
|
|
| Re: Grouping Data [message #596285 is a reply to message #596284] |
Thu, 19 September 2013 16:26 |
Duane
Messages: 557
Registered: December 2002
|
Senior Member |
|
|
Exactly. Thanks.
I was trying all sorts of combinations of count, group by, count() over partition and couldn't get what I wanted. It didn't dawn on me to use count with a decode. DUH!
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 00:31:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
