Oracle SQL Counting across multiple columns within a group

Question

Have a data set that has multiple repeating entries across columns.

ID   DATE       CLIENT   TEST0    TEST1    TEST2
================================================
1    04/12/17   123      CBC      LIPID    (null)
2    04/12/17   345      LIPID    (null)   (null)
3    04/13/17   123      BMP      CBC      (null)
4    04/13/17   345      TSH      LIPID    (null)

Ideally, I'd like the output to be grouped by client, then give test counts by the data in the TEST0, TEST1, TEST2 columns.
Should return:

CLIENT   CBC   LIPID    BMP    TSH
====================================
123      2     1        1      0
345      0     2        0      1

Using

select *
from
(
 select test0 as testid from orders
 union all
 select test1 as testid from orders
) t1
pivot
(
count(testid)
for testid in ('CBCWD','LIPID','BMP','TSH')
)

is getting me roughly at that target, but then I'm struggling to pass in other controls like limited date range, or linking out to the client table so I can translate the client code into an actual name.

Alex Poole · Answer 1 · 2017-04-20T15:33:34.207

You could unpivot the three 'test' columns into a single one, which is essentially what your union is doing; and then pivot that back to the columns you want (assuming you're on 11g or higher); with your sample data in a CTE:

with orders (ID, DT, CLIENT, TEST0, TEST1, TEST2) as (
  select 1, date '2017-04-12', 123, 'CBC', 'LIPID', null from dual
  union all select 2, date '2017-04-12', 345, 'LIPID', null, null from dual
  union all select 3, date '2017-04-13', 123, 'BMP', 'CBC', null from dual
  union all select 4, date '2017-04-13', 345, 'TSH', 'LIPID', null from dual
)
select * from (
  select client, test
  from orders
  unpivot (test for num in (test0 as '0', test1 as '1', test2 as '2'))
)
pivot (count(test) as cnt for (test)
  in ('CBC' as cbc, 'LIPID' as lipid, 'BMP' as bmp, 'TSH' as tsh));

    CLIENT    CBC_CNT  LIPID_CNT    BMP_CNT    TSH_CNT
---------- ---------- ---------- ---------- ----------
       123          2          1          1          0
       345          0          2          0          1

To filter by date, say, change from orders to another subquery that restircts the rows picked from the table. To join the result to another table, either join within that inner query, or make the whole thing another level of inner query, something like:

select c.client_name, o.cbc_cnt, o.lpid_cnt, o.dmp_cnt, o.tsh_cnt
from (
    select * from (
      select client, test
      from (
        select *
        from orders
        where dt = date '2017-04-12'
      )
      unpivot (test for num in (test0 as '0', test1 as '1', test2 as '2'))
    )
    pivot (count(test) as cnt for (test)
      in ('CBC' as cbc, 'LIPID' as lipid, 'BMP' as bmp, 'TSH' as tsh))
) o
join your_client_table c on c.client = o.client;

If a single value can only appear in on column at a time in a row - so you couldn't have a row with both test0 and test1 both set to CBC, for instance - then you could more simply do:

select client,
  count(case when test0 = 'CBC' or test1 = 'CBC' or test2 = 'CBC' then client end) as cbc,
  count(case when test0 = 'LIPID' or test1 = 'LIPID' or test2 = 'LIPID' then client end) as lipid,
  count(case when test0 = 'BMP' or test1 = 'BMP' or test2 = 'BMP' then client end) as bmp,
  count(case when test0 = 'TSH' or test1 = 'TSH' or test2 = 'TSH' then client end) as tsh
from orders
group by client;

    CLIENT        CBC      LIPID        BMP        TSH
---------- ---------- ---------- ---------- ----------
       123          2          1          1          0
       345          0          2          0          1

but it isn't clear that is the case.

score 0 · Answer 2 · edited May 23 '17 at 12:25

For queries that are looking for a certain date range, try reading here: Oracle date "Between" Query

It doesn't look like that is a very well defined table structure. You should have a single test column, not multiple ones. For what you have though, you are on the right track.

WITH ModifedTable AS (
  SELECT ID, DATE, CLIENT, TEST0 AS TEST_TYPE FROM Table WHERE TEST0 IS NOT NULL
  UNION ALL
  SELECT ID, DATE, CLIENT, TEST1 FROM Table WHERE TEST1 IS NOT NULL
  UNION ALL
  SELECT ID, DATE, CLIENT, TEST2 FROM Table WHERE TEST2 IS NOT NULL)
SELECT 
  CLIENT, 
  SUM(DECODE(TEST_TYPE, "CBC", 1, NULL)) AS CBC_COUNT,
  SUM(DECODE(TEST_TYPE, "LIPID", 1, NULL)) AS LIPID_COUNT,
  SUM(DECODE(TEST_TYPE, "BMP", 1, NULL)) AS BMP_COUNT,
  SUM(DECODE(TEST_TYPE, "TSH", 1, NULL)) AS TSH_COUNT
FROM ModifiedTable
GROUP BY CLIENT
ORDER BY CLIENT

If you want a specific date range, filter it out inside the ModifiedTable subquery, or use a second subquery to filter:

WITH ModifiedTable AS (SELECT .... ),
ModifiedTableDates AS (SELECT * FROM ModifiedTable WHERE DATE BETWEEN date1 AND date2)
SELECT ... FROM ModifiedTableDates

Oracle SQL Counting across multiple columns within a group

2 Answers2