SQL Counting the number of occurence based on a subject

Question

I find it hard to word what I am trying to achieve. I have a table that looks like this:

user char
---------
a | x
a | y
a | z
b | x
b | x
b | y
c | y
c | y
c | z

How do I write a query that would return me the following result?

user x y z
-------
a |1|1|1|
b |2|1|0|
c |0|2|1|

the numbers represent the no of occurences of chars in the original table

EDIT: The chars values are unknown hence the solution cannot be restricted to these values. Sorry for not mentioning it sooner. I am using Oracle DB but planning to use JPQL to construct the query.

Are the possible `char` values limited to `x`, `y` and `z`, or could they be any character? — Mureinik, Dec 30 '13 at 18:01
Sorry for not mentioning this sooner, the char values are unknown hence it can be any character. At Vland and OldProgrammer, I am using oracle DB and will be using JPQL to construct the query — Danny Saksono, Dec 30 '13 at 18:36
without using XML, not sure if you can PIVOT with an unknown set of values (assuming you're looking for an SQL only solution) — tbone, Dec 30 '13 at 19:56
possible duplicate of [Dynamic Oracle Pivot\_In\_Clause](http://stackoverflow.com/questions/16978047/dynamic-oracle-pivot-in-clause) — Yaroslav Shabalin, Dec 30 '13 at 20:38

John Chrysostom · Answer 1 · 2013-12-30T19:54:38.107

6

select user,
    sum(case when char='x' then 1 else 0 end) as x,
    sum(case when char='y' then 1 else 0 end) as y,
    sum(case when char='z' then 1 else 0 end) as z
from thetable
group by user

Or, if you don't mind stacking vertically, this solution will give you a solution that works even with unknown sets of characters:

select user, char, count(*) as count
from thetable
group by user, char

This will give you:

user   char   count
a      x      1
a      y      1
a      z      1
b      x      2

If you want to string an unknown set of values out horizontally (as in your demo output), you're going to need to get into dynamic queries... the SQL standard is not designed to generate output with an unknown number of columns... Hope this is helpful!

edited Dec 30 '13 at 19:54

answered Dec 30 '13 at 18:01

John Chrysostom

3,973
1
34
50

1

What if he has 250 possible char values? – Vland Dec 30 '13 at 18:10
1

Then that should be mentioned in the question. – Zane Dec 30 '13 at 18:19
Sorry for not mentioning this sooner, the char values are unknown hence it can be any character. – Danny Saksono Dec 30 '13 at 18:37
Do you definitely need the result strung out horizontally? 'Cause it would be very easy to stack it vertically, even with unknown values. – John Chrysostom Dec 30 '13 at 18:42
Sorry I can't visualize what you mean by stacking it vertically. Would you mind giving me an example of how it would look like? I probably could post-process the data in Java if it is not too far fetched. – Danny Saksono Dec 30 '13 at 18:45
You could have three columns: user, character, and count. So, each row would be a unique user/character combination, with a count of how many times that occurred. – John Chrysostom Dec 30 '13 at 19:06

score 0 · Answer 2 · edited May 23 '17 at 11:56

Another option, using T-SQL PIVOT (SQL SERVER 2005+)

select *
from userchar as t
pivot
(
    count([char]) for [char] in ([x],[y],[z]) 
) as p

Result:

user        x           y           z
----------- ----------- ----------- -----------
a           1           1           1
b           2           1           0
c           0           2           1

(3 row(s) affected)

Edit ORACLE:

You can build a similar PIVOT table using ORACLE.

The tricky part is that you need the right column names in the IN ([x],[y],[z],...) statement. It shouldn't be too hard to construct the SQL query in code, getting a (SELECT DISTINCT [char] from table) and appending it to your base query.

Pivoting rows into columns dynamically in Oracle

score 0 · Answer 3 · answered Dec 30 '13 at 20:14

If you don't know the exact values on which to PIVOT, you'll either need to do something procedural or mess with dynamic sql (inside an anonymous block), or use XML (in 11g).

If you want the XML approach, it would be something like:

with x as (
  select 'a' as usr, 'x' as val from dual
  union all
  select 'a' as usr, 'y' as val from dual
  union all
  select 'b' as usr, 'x' as val from dual
  union all
  select 'b' as usr, 'x' as val from dual
  union all
  select 'c' as usr, 'z' as val from dual
)
select * from x
pivot XML (count(val) as val_cnt for val in (ANY))
;

Output:

USR VAL_XML
a   <PivotSet><item><column name = "VAL">x</column><column name = "VAL_CNT">1</column></item><item><column name = "VAL">y</column><column name = "VAL_CNT">1</column></item></PivotSet>
b   <PivotSet><item><column name = "VAL">x</column><column name = "VAL_CNT">2</column></item></PivotSet>
c   <PivotSet><item><column name = "VAL">z</column><column name = "VAL_CNT">1</column></item></PivotSet>

Hope that helps

SQL Counting the number of occurence based on a subject

3 Answers3