Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: Join 2 queries which are results from different dates

    Hi,

    Any idea how to do this? - i need to get a separate count from 2 or more different days of some information in a table which has rows of date, name and description many of which repeated e.g

    select a.name, a.description, a.count(description) as today
    from table a
    where a.date = sysdate
    group by a.description, a.name;

    i also need the same query results for sysdate-1, -2 etc for maybe the last 4-7 days.

    I want the results columns to look like:
    name, description, today (count), tuesday(count), monday(count)...etc...

    so i can compare the counts of names and descriptions on each day..

    Any ideas?

    Thanks

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Try something like:
    Code:
    select a.name, a.description,
           sum(case when a.date = sysdate then 1 else 0 end) as today,
           sum(case when a.date = sysdate - 1 then 1 else 0 end) as yesterday
           ...
    from table a
    where a.date >= sysdate - 6
    group by a.description, a.name;
    

  3. #3
    Join Date
    Mar 2011
    Posts
    4
    Superb!...
    Runs really faster as well, was pulling my hair out with sub queries.
    Many thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The function SUM(in this case) can be replaced by COUNT, like...
    Code:
    ...
           COUNT(case when a.date = sysdate then 0 end) as today,
    ...
    Because, only non-null values are subject of aggregate functions(e.g. COUNT).
    (in other words, COUNT counts only non-null values.)

  5. #5
    Join Date
    May 2014
    Posts
    24
    I guess you should just declare it as a non-value in that code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •