SQL query to count number of objects in each state on each day

Question

Given a set of database records that record the date when an object enters a particular state, I would like to produce a query that shows how many objects are in each state on any particular date. The results will be used to produce trend reports showing how the number of objects in each state changes over time.

I have a table like the following that records the date when an object enters a particular state:

ObjID EntryDate  State
----- ---------- -----
    1 2014-11-01   A
    1 2014-11-04   B
    1 2014-11-06   C
    2 2014-11-01   A
    2 2014-11-03   B
    2 2014-11-10   C
    3 2014-11-03   B
    3 2014-11-08   C

There are an arbitrary number of objects and states.

I need to produce a query that returns the number of objects in each state on each date. The result would look like the following:

Date       State Count
---------- ----- -----
2014-11-01   A       2
2014-11-01   B       0
2014-11-01   C       0
2014-11-02   A       2
2014-11-02   B       0
2014-11-02   C       0
2014-11-03   A       1
2014-11-03   B       2
2014-11-03   C       0
2014-11-04   A       0
2014-11-04   B       3
2014-11-04   C       0
2014-11-05   A       0
2014-11-05   B       3
2014-11-05   C       0
2014-11-06   A       0
2014-11-06   B       2
2014-11-06   C       1
2014-11-07   A       0
2014-11-07   B       2
2014-11-07   C       1
2014-11-08   A       0
2014-11-08   B       1
2014-11-08   C       2
2014-11-09   A       0
2014-11-09   B       1
2014-11-09   C       2
2014-11-10   A       0
2014-11-10   B       0
2014-11-10   C       3

I'm working with an Oracle database.

I haven't been able to find an example that matches my case. The following questions look like they are asking for solutions to similar but different problems:

• SQL Count Of Open Orders Each Day Between Two Dates
• Mysql select count per category per day

Any help or hints that can be provided would be much appreciated.

Serene

Your expected results include data not in your table... where does this come from? – Irreligion

The database records the date on which an object enters a particular state. The object remains in that state until it transitions to another state. Ideally, the query results will provide the number of objects in each state even for dates on which no state transitions occurred. – Serene

The first part of this, which is arguably the tricky bit, is the data densification - you need to project a result which is a cross-join of every state and every date (including the dates that are not present). Oracle has specific, but little known, data densification syntax for this: the "partition join syntax" described in the documentation here docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/…. – Hildy

Answer 1

SELECT EntryDate AS "Date", State, COUNT(DISTINCT ObjectId) AS "Count" GROUP BY EntryDate, State ORDER BY EntryDate, State;

Pressman

Answer 2

As each state is not recorded every date , you need to do CROSS JOIN to get the unique states and then do GROUP BY.

SELECT EntryDate, 
       C.State, 
       SUM(case when C.state = Table1.state then 1 else 0 end) as Count
FROM Table1
CROSS JOIN ( SELECT DISTINCT State FROM Table1) C
GROUP BY EntryDate, C.State
ORDER BY EntryDate

Endomorph

Answer 3

This query will list how many objects ENTERED a particular state on each day, assuming each object only changes state ONCE a day. If objects change state more than once a day, you would need to use count(distinct objid):

select entrydate, state, count(objid) 
from my_table
group by entrydate, state
order by entrydate, state

However, you are asking how many objects ARE in a particular state on each day, thus you would need a very different query to show that. Since you only provide that particular table in your example, I'll work with that table only:

select alldatestates.entrydate, alldatestates.state, count(statesbyday.objid)
from
    (
    select alldates.entrydate, allstates.state
    from (select distinct entrydate from mytable) alldates,
         (select distinct state from mytable) allstates
    ) alldatestates
    left join
    (
    select alldates.entrydate, allobjs.objid, (select min(state) as state from mytable t1 
                                          where t1.objid = allobjs.objid and 
                                                t1.entrydate = (select max(entrydate) from mytable t2 
                                                                where t2.objid = t1.objid and
                                                                      t2.entrydate <= alldates.entrydate)) as state
    from (select distinct entrydate from mytable) alldates,
         (select distinct objid from mytable) allobjs
    ) statesbyday
    on alldatestates.entrydate = statesbyday.entrydate and alldatestates.state = statesbyday.state
group by alldatestates.entrydate, alldatestates.state
order by alldatestates.entrydate, alldatestates.state

Of course, this query will be much simpler if you have a table for all the possible states and another one for all the possible object ids.

Also, probably you could find a query simpler than that, but this one works. The downside is, it could very quickly become an optimizer's nightmare! :)

Unconstitutional

Answer 4

Try this query :

select EntryDate As Date, State, COUNT(ObjID) AS Count from table_name
GROUP BY EntryDate , State 
ORDER BY State

History

Answer 5

You can try this with analytic function as well:

Select
Date,
State,
count(distinct obj) OVER (PARTITION BY EntryDate, State) count
from table
order by 1;

Pisciculture

Answer 6

This is possibly a pretty awful answer, but it's been decomposed to a fare-the-well so the reader can run any section of it they like to see how the results evolve step-by-step. I'm not sure I'd want to maintain something like this, but it gives the right answers and makes sense.

with t as 
(select 1 as objid, date '2014-11-01' as entry_date, 'A' as state from dual union all
 select 1, date '2014-11-04', 'B' from dual union all
 select 1, date '2014-11-06', 'C' from dual union all
 select 2, date '2014-11-01', 'A' from dual union all
 select 2, date '2014-11-03', 'B' from dual union all
 select 2, date '2014-11-10', 'C' from dual union all
 select 3, date '2014-11-03', 'B' from dual union all
 select 3, date '2014-11-08', 'C' from dual
),
date_range as
(select min(entry_date) as min_entry_date, max(entry_date) as max_entry_date
   from t
),
-- generate the full range of dates in t
date_values as 
(select min_entry_date + level - 1 as date_value
   from date_range
 connect by level <= max_entry_date - min_entry_date + 1
),
-- get all the leads and lags of entry_dates and states so we can track changes
t2 as
(
select entry_date, state, objid, min_entry_date, max_entry_date,
       lead(state) over (partition by objid order by entry_date) as next_state,
       lag(state) over (partition by objid order by entry_date) as prior_state,
       lead(entry_date) 
          over (partition by objid order by entry_date) as next_entry_date ,
       lag(entry_date) 
          over (partition by objid order by entry_date) as prior_entry_date 
  from t 
       cross join date_range
),
-- put the min-max entry dates into the query so we don't have awful nulls
t3 as
(select t2.entry_date, t2.state, objid, t2.next_state, t2.prior_state, 
        coalesce(t2.next_entry_date, t2.max_entry_date) as next_entry_date, 
        coalesce(t2.prior_entry_date, t2.min_entry_date) as prior_entry_date
  from t2
),  
-- this is where we determine whether or not a row is "in the date range"
t4 as
(
select entry_date, date_value, state, objid, next_state, prior_state, next_entry_date, prior_entry_date,
       case when date_value >= entry_date 
             and (   date_value < next_entry_date
                  or next_state is null )
            then 1 else 0 end as this_row_counts
  from t3
       cross join date_values
)
-- a nice simple grouping
select date_value, state, sum(this_row_counts) as state_count
 from t4 
group by date_value, state
order by date_value, state;

Disjoin

Was this decade-old question with 8 answers really crying out for another "pretty awful" answer? – Guanaco

Well, I thought it was an interesting question without an accepted answer, and I tried to make it a teaching moment. Thanks EVER so much for your input. – Disjoin

Answer 7

Select EntryDate as Date, State, Count(Distinct ObjID) as Count From Table_1 Group by EntryDate, State

Soileau

I an understand giving this answer. I can't understand the upvote. Clearly, this produces nothing at all similar to what the OP is asking for. – Picture

Answer 8

I'm going to do a quick and dirty way to get numbers. You can choose your preferred method . . . using recursive CTEs, connect by, or a numbers table. So, the following generates the all combinations of the dates and states. It then uses a correlated subquery to count the number of objects in each state on each date:

with n as (
      select rownum - 1 as n
      from table t
     ),
     dates as (
      select mind + n.n
      from (select min(date) as mind, max(date) as maxd from table) t
      where mind + n.n <= maxd
     )
select d.date, s.state,
       (select count(*)
        from (select t2.*, lead(date) over (partition by ObjId order by date) as nextdate
              from table t2
             ) t2
        where d.date >= t2.date and (d.date < t2.nextdate or t2.nextdate is null) and
              d.state = t2.state
       ) as counts
from dates d cross join
     (select distinct state from table t)

Picture

Answer 9

Working out of SQL SERVER because I'm more familiar, but here's what I've got so far:

fiddle example (SQL SERVER but the only difference should be the date functions I think...): http://sqlfiddle.com/#!3/8b9748/2

WITH zeroThruNine AS (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9), 
nums AS (SELECT 10*b.n+a.n AS n FROM zeroThruNine a, zeroThruNine b), 
Dates AS (
    SELECT DATEADD(d,n.n,(SELECT MIN(t.EntryDate) FROM @tbl t)) AS Date
    FROM nums n
    WHERE DATEADD(d,n.n,(SELECT MIN(t.EntryDate) FROM @tbl t))<=(SELECT MAX(t.EntryDate) FROM @tbl t)
), Data AS (
    SELECT d.Date, t.ObjID, t.State, ROW_NUMBER() OVER (PARTITION BY t.ObjID, d.Date ORDER BY t.EntryDate DESC) as r
    FROM Dates d, @tbl t
    WHERE d.Date>=t.EntryDate
)
SELECT t.Date, t.State, COUNT(*)
FROM Data t
WHERE t.r=1
GROUP BY t.Date, t.State
ORDER BY t.Date, t.State

First, start off making a numbers table (see http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html) for examples. There are different ways to create number tables in different databases, so the first two WITH expressions I've created are just to create a view of the numbers 0 through 99. I'm sure there are other ways, and you may need more than just 100 numbers (representing 100 dates between the first and last dates you provided)

So, once you get to the Dates CTE, the main part is the Data CTE

It finds each date from the Dates cte, and pairs it with the values of the @tbl table (your table) with any States that were recorded after said date. It also marks the order of which states/per objid in decreasing order. That way, in the final query, we can just use WHERE t.r=1 to get the max state for each objid per date

One issue, this gets data for all dates, even those where nothing was recorded, but for zero-counts, it doesn't return anything. If you wanted to, you could left join this result with a view of distinct states and take 0 when no join was made

Overtrade