Comparing 2 SELECT counts - SQL

I have 2 select counts and want to see it one of them is less than 90% of the other.

The first select count is yesterday's data from Table T1, say X
The second select count is today's data from same Table T1, say Y

I want to see if Y < 90%(X)

How do I do this?
codemonkey2480Asked:
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.

sventhanCommented:
Do you have both X & Y on the same table?
0
codemonkey2480Author Commented:
Yes
0
cyberkiwiCommented:
select
sum(case when datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) then valuecol else 0 end) YesterdayTotal,
sum(case when datecol >= trunc(sysdate) and datecol < trunc(sysdate) +1 then valuecol else 0 end) TodayTotal,

sum(case when datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) then valuecol else 0 end) YesterdayTotal
 <
0.9 *
sum(case when datecol >= trunc(sysdate) and datecol < trunc(sysdate) +1 then valuecol else 0 end)  As IsLessThan90PercentOfToday

from tbl
where datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

Open in new window


The third column is either true or false
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cheers4beersCommented:
Are you wanting to do this as a comparison with one query?  If so, you should create a temporary table, select the values that you are wanting to compare into the temp table, then use PL/SQL to compare the two:

DECLARE
   x := <value of x>;
   y := <value of y>;
BEGIN
   IF y < (x*.9) THEN
      DBMS_OUTPUT.PUT_LINE('The value '||Y||' is less than 90% of '||X||'.');
   END IF;
END;
/
0
codemonkey2480Author Commented:
cyberkiwi:
What are datecol and valuecol?


cheers4beers,
Yes I want to compare 2 SELECT count queries.
Any reason I need to use a PL/SQL block and temp tables?
0
cyberkiwiCommented:
datecol : the date column? for example time of sale
valuecol : where you are summing up values from... counting from, in which case ignore this. It should just be 1 for each record
select
sum(case when datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when datecol >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end) TodayTotal,

sum(case when datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) then 1 else 0 end) YesterdayTotal
 <
0.9 *
sum(case when datecol >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end)  As IsLessThan90PercentOfToday

from tbl
where datecol >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

Open in new window

0
sventhanCommented:
try this...

      SELECT x,y, (case when y < (x*.9) then 'Y' else 'N' end) as compare
        FROM yourtable
0
johnsoneSenior Oracle DBACommented:
You don't have a lot of details, but if I understand what you are looking for this should be it.

select x, y, (x*0.9) 90x
from (select count(1) x from ....) x, (select count(1) y from ...) y;

You need to put in your queries for X and Y.
0
codemonkey2480Author Commented:
cyberkiwi:
Here is my SQL with actual column name:
select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal,
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal
 <
0.9 *
sum(case when loaddt >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end)  As IsLessThan90PercentOfToday
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

I get an error "ORA-00923: FROM keyword not found where expected"

0
sventhanCommented:
I thought your x and y are counts that are already stored in a table.
0
cyberkiwiCommented:
Okay, please try this instead

select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal,
CASE WHEN
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal
 <
0.9 *
sum(case when loaddt >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end)  
THEN 1 ELSE 0 END As IsLessThan90PercentOfToday
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

Open in new window

0
johnsoneSenior Oracle DBACommented:
OK, not at all what I got from the descriptions posted.

The problem is you cannot have column aliases in the middle of a calculation.  The query should be:

select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal,
case when sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end)
 <
0.9 *
sum(case when loaddt >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end) then 1 else 0 end  As IsLessThan90PercentOfToday
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

However, I think you are overdoing the calculations here.  You should be looking more like this:

sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal
 <
0.9 *
sum(case when loaddt >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end)  As IsLessThan90PercentOfToday

select yesterdaytotal, todaytotal, case when yesterdaytotal < 0.9 * todaytotal then 1 else 0 end as IsLessThan90PercentOfToday
from (
select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1) e
;
0
johnsoneSenior Oracle DBACommented:
Reposting as there was a past error that made it confusing.  It should be this:

OK, not at all what I got from the descriptions posted.

The problem is you cannot have column aliases in the middle of a calculation.  The query should be:

select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal,
case when sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end)
 <
0.9 *
sum(case when loaddt >= trunc(sysdate) and datecol < trunc(sysdate) +1 then 1 else 0 end) then 1 else 0 end  As IsLessThan90PercentOfToday
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1

However, I think you are overdoing the calculations here.  You should be looking more like this:

select yesterdaytotal, todaytotal, case when yesterdaytotal < 0.9 * todaytotal then 1 else 0 end as IsLessThan90PercentOfToday
from (
select
sum(case when loaddt >= trunc(sysdate) -1 and loaddt < trunc(sysdate) then 1 else 0 end) YesterdayTotal,
sum(case when loaddt >= trunc(sysdate) and loaddt < trunc(sysdate) +1 then 1 else 0 end) TodayTotal
from employee
where loaddt >= trunc(sysdate) -1 and datecol < trunc(sysdate) +1) e
;
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
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.