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.
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

Open in new window

mjoseph23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
put a distinct around your original query...

select distinct * from (your query here)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:
can you please be more specific... what you have in the original table, and what you want as a result?
0
dave4dlCommented:
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)

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mjoseph23Author Commented:
the result i was trying to get was simply

strs1  strs2  stm1  stm2
0         1         1       1
0
HainKurtSr. System AnalystCommented:
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

Open in new window

0
HainKurtSr. System AnalystCommented:
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

Open in new window

0
HainKurtSr. System AnalystCommented:
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....
0
mjoseph23Author Commented:
ok, that works too. and it is more correct since i am not selecting any columns in my main query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.