1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Two count sql queries

Discussion in 'SQL PL/SQL' started by ketangarg86, Mar 12, 2015.

  1. ketangarg86

    ketangarg86 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.

    I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.

    Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.

    Please help. I am relatively new to Oracle SQL.

    Thanks
    Ketan
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    --1
    SELECT ord_date,
           COUNT(*) ord_count
    FROM orders
    GROUP BY ord_date
    HAVING ord_date = trunc(sysdate - 3)
        OR ord_date = trunc(sysdate)
    --2
    SELECT COUNT(CASE
                    WHEN ord_date = trunc(sysdate -3) THEN
                       1
                 END) count_3_days_ago,
           COUNT(CASE
                    WHEN ord_date = trunc(sysdate) THEN
                       1
                 END) count_today      
    FROM orders