Trying to work out the question (SQL: query to show how many users have each property (grouped)) in oracle 12c.
with data (id, name, a, b, c, d) as
(
select 1, 'name1', 'yes', 'yes', '', 'yes' from dual union all
select 2, 'name2', 'yes', '', '', 'yes' from dual union all
select 3, 'name3', '', 'yes', '', 'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '', 'yes' from dual union all
select 5, 'name5', '', '', 'yes', 'yes' from dual
)
,
coll (a,b,c,d) as
(
select count(a) a, count(b) b, count(c) c, count(d) d from data
)
select * from coll
unpivot
(
val for (col) in (a, b, c, d)
);
works fine and produces the desired result.
Whereas
with data (id, name, a, b, c, d) as
(
select 1, 'name1', 'yes', 'yes', '', 'yes' from dual union all
select 2, 'name2', 'yes', '', '', 'yes' from dual union all
select 3, 'name3', '', 'yes', '', 'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '', 'yes' from dual union all
select 5, 'name5', '', '', 'yes', 'yes' from dual
)
--,
--coll (a,b,c,d) as
--(
select count(a) a, count(b) b, count(c) c, count(d) d from data -- Line 1685
--)
--select * from coll
unpivot
(
val for (col) in (a, b, c, d)
);
produces the below error.
ORA-00904: "D": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 1,685 Column: 50
Can someone please help to find why it is so?
The queries in question are different.
1) In the first query, you are counting the number of non-null's in respective columns initially and unpivoting them.
2) In the second, you are unpivoting first, and trying to count them. After you unpivot there are no columns a,b,c,d. They will be under col..as you are saying val for col in (a,b,c,d). To get the same result as the first, use
select col,count(*) as val
from data -- Line 1685
unpivot (
val for col in (a,b,c,d)
)
group by col