Oracle Statement to fetch data and put results in columns-CodePudding

I know maybe my question is kinda weird but I didn't use Oracle before, I have this statement to select count of specific number in column and output result

select count(*)as Step1 from ALLVALUES WHERE NAME LIKE '%VALUE%' AND systime between caltosys(&t1) and caltosys(&t2) AND ROWNUM <= 90000 AND VALUE ='48'

This will output the results like this

------
Step1
------
20
------

I need to fetch multiple conditions and put each result in column for example

select count(*)as Step1 from ALLVALUES WHERE NAME LIKE '%VALUE%' AND systime between caltosys(&t1) and caltosys(&t2) AND ROWNUM <= 90000 AND VALUE ='48'
select count(*)as Step2 from ALLVALUES WHERE NAME LIKE '%VALUE%' AND systime between caltosys(&t1) and caltosys(&t2) AND ROWNUM <= 90000 AND VALUE ='49'
...

And output belike this

------------------------------
Step1 | Step2 | Step3 | Step4
------------------------------
20  | 5     | 1     | 4
------------------------------

Is that possible in my current situation?

I tried to write as suggested statement

select to_char(systocal(systime),'yyyy-mm-dd hh24:mi:ss.ff3') as Time,
COUNT(case WHEN value ='48.8' then value end) as Step1,
COUNT(CASE WHEN value ='48.75' then value end) as Step2,
COUNT(CASE WHEN value ='48.7' then value end)as Step3,
COUNT(CASE WHEN value ='48.68' then value end)as Step4,
COUNT(CASE WHEN value ='48.6' then value end)as Step5,
COUNT(CASE WHEN value ='48.58' then value end)as Step6,
COUNT(CASE WHEN value ='48.55' then value end)as Step7,
COUNT(CASE WHEN value ='48.52' then value end)as Step8,
from MEA where ID = 5570 AND systime between caltosys(&t1) and caltosys(&t2) AND ROWNUM <= 100

&t1 - &t2 are variables for time

But keep giving me missing expression 936

CodePudding user response：

You can use conditional aggregation, relaxing the filter in the where clause in include all the values you're interesting in, and case expressions to only count matching rows:

select
  count(case when value = '48' then value end) as Step1,
  count(case when value = '49' then value end) as Step2,
  count(case when value = '50' then value end) as Step3,
  count(case when value = '51' then value end) as Step4
from ALLVALUES WHERE NAME LIKE '%VALUE%'
AND systime between caltosys(&t1) and caltosys(&t2)
AND ROWNUM <= 90000
AND VALUE in ('48', '49', '50', '51')

You could also use the pivot clause to achieve the same thing.

The rownum filter looks odd though - partly because of the high number, but mostly because you're applying it on an unordered result set, so the results will be indeterminate (if there are more than 90000 matching rows anyway).

CodePudding user response：

You can use PIVOT to reorganise rows to columns. One way to write your query (omitting the time columns for simplicity) would be

select * from
(select value from MEA where ID = 5570)
pivot
(count(*) for value in 
  (
    '48.8' as Step1,
    '48.75' as Step2,
    '48.7' as Step3,
    '48.68' as Step4,
    '48.6' as Step5,
    '48.58' as Step6,
    '48.55' as Step7,
    '48.52' as Step8
  )
);

See the Pivoting Operations chapter of the Oracle Database Data Warehousing Guide for more information and examples.