2

i need one single query which will give result like the one i give below

createddate  recordcount  acceptdate    submitdate   createddate  
27-MAR-16                   24              36          11
28-MAR-16                   79              207         58

for reference i am providing some queries which i want to merge into one single query

    select trim(date_created) createddate,count(*) recordcount
    from man
    where status IN ('CREATED')and date_created>sysdate-15  
    group by trim(date_created) ORDER BY     TO_DATE(createddate,'DD/MM/YYYY');

this query will result like the following.

createddate recordcount
27-MAR-16       11
28-MAR-16       58

the second query 

select trim(DATE_SUB) submitdate,count(*) recordcount
from man
where status IN ('SUBMITTED')and DATE_SUB>sysdate-15  
group by trim(date_sub) ORDER BY TO_DATE(submitdate,'DD/MM/YYYY');

result of this query is like

submitdate recordcount
27-MAR-16   36
28-MAR-16   207

and the third query is like - 

select trim(DATE_PUB) acceptdate,count(*) recordcount
from man
where status IN ('ACCEPTED')and DATE_PUB>sysdate-15  
group by trim(DATE_PUB) ORDER BY TO_DATE(acceptdate,'DD/MM/YYYY');

acceptdate  recordcount
 27-MAR-16      24
 28-MAR-16      79

how can i merger these three query so that i can get count for all in single query?which will give me result like

createddate  recordcount  acceptdate    submitdate   createddate  
27-MAR-16                   24              36          11
28-MAR-16                   79              207         58
Improve this question
2
  • 1
    The only difference I can see is the lack of group by in the second query. Can you add some sample data that help us reproduce the issue? Commented Apr 11, 2016 at 8:16
  • 4
    The first query filters by date_c and the second by date_p. They could be different. Commented Apr 11, 2016 at 8:22

2 Answers 2

Reset to default
2

Your first query where clause has date but second query where clause has DATE_P.

Try like this

SELECT Trim(date) createddate,
   COUNT(*) recordcount,
   SUM(case when status = 'A' then 1 else 0 end) as a,
   SUM(case when status = 'S' then 1 else 0 end) as s,
   SUM(case when status = 'C' then 1 else 0 end) as c,
   SUM(case when status = 'R' then 1 else 0 end) as r
FROM man 
WHERE status IN ('A','S','C','R')and date >sysdate-15
GROUP BY trim(date) ORDER BY createddate;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Count ignores nulls so that should give the same result as the original query.
i updeated my question for better understanding.the now can you provide me solution for this
@suraha Your first query where clause has date but second query where clause has DATE_P. Which one is correct?
both fields are diff..and both are exist
@VigneshKumar no its not working, status of 'a' i can get only from select Count(*) From Man where STATUS = 'A' and DATE_P>sysdate-15; and status of others i can get from first query where date and date_p are two diff fields
|
1

You seem to want to get counts for each status type, for each day. The first step is generate all the dates you're interested in, which you can do with:

select trunc(sysdate) + 1 - level as dt
from dual
connect by level <= 15;

You can then (outer) join to your actual table where any of the three date columns match a generated date, and expand your case conditions to check which one you're looking at:

with t as (
  select trunc(sysdate) + 1 - level as dt
  from dual
  connect by level <= 15
)
select t.dt,
  count(*) as recordcount,
  count(case when status = 'ACCEPTED' and trunc(m.date_pub) = t.dt
    then 1 end) as acceptdate,
  count(case when status = 'SUBMITTED' and trunc(m.date_sub) = t.dt
    then 1 end) as submitdate,
  count(case when status = 'CREATED' and trunc(m.date_created) = t.dt
    then 1 end) as createddate
from t
left join man m
on (m.date_pub >= t.dt and m.date_pub < t.dt + 1)
or (m.date_sub >= t.dt and m.date_sub < t.dt + 1)
or (m.date_created >= t.dt and m.date_created < t.dt + 1)
group by t.dt
order by t.dt;

I've used range checks for the join conditions - it isn't clear if all your date columns are set at midnight, but it's safer to assume they might have other times and you cant everything from the matching day.

Each of the three count results is now only of those rows which match the status and where the specific date column matches, which I think is what you want. I've used trunc() here instead of a range comparison, as it doesn't have the potential performance penalty you can see in the where clause (from it potentially stopping an index being used).

This may throw out your recordcount though, depending on your actual data, as that will include rows that now might not match any of the case conditions. You can repeat the case conditions, or use an inline view to calculate the total of the three individual counts, depending on what you want it to include and what will be the easiest for you to maintain. If those are the only three statuses in your table then it may be OK with count(*) but check it gets the value you expect.

Improve this answer

4 Comments

i updated my query and question for better understanding can you please look into this now
That's not an update, that's a significant rewrite... as far as I can see this answer still applies, you just have three dates to think about instead of two?
the result from your solution is not valid for my case because in every status have all the fields in db like date)sub and date_created and all..so in that case when in came in where condition it will never go to or condition this means i am getting count of first condition of where insted of or if required
@suraha - not sure I follow that, there are four separate cases, not a single case where it can only follow one branch. Anyway, I've updated to what i think you mean now, I've included a date-generating CTE so you get rows where all the counts are zero. Otherwise it's essentially the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.