mjoseph23
asked on
how do you select distinct on a row or is their another way to get unique row
I am doing a select statement that is retrieving the following.
strs1 strs2 stm1 stm2
0 1 1 1
0 1 1 1
0 1 1 1
0 1 1 1
0 1 1 1
I want to only retrieve one row of results because I am doing a report on how many of a particular value in a particular column in a table.
strs1 strs2 stm1 stm2
0 1 1 1
0 1 1 1
0 1 1 1
0 1 1 1
0 1 1 1
I want to only retrieve one row of results because I am doing a report on how many of a particular value in a particular column in a table.
select
(select count(*) from tblload a where a.sterilizer_val = 1) as strs1,
(select count(*) from tblload b where b.sterilizer_val = 2) as strs2,
(select count(*) from tblload c where c.sterilizer_val = 3) as stm1,
(select count(*) from tblload d where d.sterilizer_val = 4) as stm2
from tblload l
order by l.sterilizer_val
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
HainKurt
can you please be more specific... what you have in the original table, and what you want as a result?
Try the following
select
(select count(*) from tblload a where a.sterilizer_val = 1) as strs1,
(select count(*) from tblload b where b.sterilizer_val = 2) as strs2,
(select count(*) from tblload c where c.sterilizer_val = 3) as stm1,
(select count(*) from tblload d where d.sterilizer_val = 4) as stm2
from tblload l
order by l.sterilizer_val
group by
(select count(*) from tblload a where a.sterilizer_val = 1),
(select count(*) from tblload b where b.sterilizer_val = 2),
(select count(*) from tblload c where c.sterilizer_val = 3),
(select count(*) from tblload d where d.sterilizer_val = 4)
ASKER
the result i was trying to get was simply
strs1 strs2 stm1 stm2
0 1 1 1
strs1 strs2 stm1 stm2
0 1 1 1
try this
select
(select count(*) from tblload a where a.sterilizer_val = 1) as strs1,
(select count(*) from tblload b where b.sterilizer_val = 2) as strs2,
(select count(*) from tblload c where c.sterilizer_val = 3) as stm1,
(select count(*) from tblload d where d.sterilizer_val = 4) as stm2
from dual
or even better one
select
sum(decode(sterilizer_val, 1, 1, 0)) as strs1,
sum(decode(sterilizer_val, 2, 1, 0)) as strs2,
sum(decode(sterilizer_val, 3, 1, 0)) as strs3,
sum(decode(sterilizer_val, 4, 1, 0)) as strs4
from tblload
try the last one.. if it is the one that you want, I recommend using that one, which is simple and it should run much faster that all others....
ASKER
ok, that works too. and it is more correct since i am not selecting any columns in my main query.