Interesting problem??? multiple COUNT(*) in one SQL statement 
Author Message
 Interesting problem??? multiple COUNT(*) in one SQL statement

Hello

I have a rather large database (over 600,000 rows) which basically contains
a number of system events that have occurred during the day (we get about
64,000 events per day, and we keep 10 working days worth of data in the
table).

The actions are categorised by priority (from 1 to 6) and originator (from 1
to 10, each number is allocated to a sales customer services team).

So basically, each row would look like:
    TimeStamp, Priority, Originator, EventID, EventStatus

I would like to create an SQL statement that will break up the day into 5
minute slots (288 per day in total), and count the number of events for each
priority and originator for that 5 minute slot.

Right now, I'm trying something like:
    SELECT
     (SELECT count(*) FROM tblEvents
      WHERE Priority=7

     ) AS '7',
     (SELECT count(*) FROM tblEvents
      WHERE Priority=6

     ) AS '6',
     (and so on)


minutes, and the above is inserted into another table. Right now, this
process runs as a job once every hour, and appends 12 new rows every hour
(12 5-minute periods per hour).

Is there a better way of doing this on the fly? i.e. using a single SQL
query to retrieve all the data, rather than have to use a batch process?

Thanks

Alex M
< a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 03:57:05 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement

Alexis,

  Here is a generic count-by-intervals script.  I hope it helps.

create function Intervals (




  StartPeriod datetime primary key,
  EndPeriod datetime
) as begin





  end
  return
end
go

select count(OrderID) as OrderCount,
       StartPeriod as [From],
       EndPeriod as [To]
       from northwind..orders right outer join
dbo.Intervals('1996-01-01','2000-01-01',7776000)
on OrderDate >= StartPeriod
and OrderDate < EndPeriod
group by StartPeriod, EndPeriod

drop function Intervals

Steve Kass
Drew University

Quote:

> Hello

> I have a rather large database (over 600,000 rows) which basically contains
> a number of system events that have occurred during the day (we get about
> 64,000 events per day, and we keep 10 working days worth of data in the
> table).

> The actions are categorised by priority (from 1 to 6) and originator (from 1
> to 10, each number is allocated to a sales customer services team).

> So basically, each row would look like:
>     TimeStamp, Priority, Originator, EventID, EventStatus

> I would like to create an SQL statement that will break up the day into 5
> minute slots (288 per day in total), and count the number of events for each
> priority and originator for that 5 minute slot.

> Right now, I'm trying something like:
>     SELECT
>      (SELECT count(*) FROM tblEvents
>       WHERE Priority=7

>      ) AS '7',
>      (SELECT count(*) FROM tblEvents
>       WHERE Priority=6

>      ) AS '6',
>      (and so on)


> minutes, and the above is inserted into another table. Right now, this
> process runs as a job once every hour, and appends 12 new rows every hour
> (12 5-minute periods per hour).

> Is there a better way of doing this on the fly? i.e. using a single SQL
> query to retrieve all the data, rather than have to use a batch process?

> Thanks

> Alex M
> < a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 04:21:47 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement
Steve,

Thanks... my script is very similar to this (maybe not as elegant).

My problem is that I want to return the number of events that happened for
each priority separately, but in the same script... perhaps an example would
be in order (very simplified):
Time    Priority
10:00        1
10:01        2
10:02        3
10:03        3
10:04        2
10:05        1
10:06        2
10:07        1
10:08        1
10:09        2

I would like the SQL query to return something like (for 5 minute
intervals):
    start, end, count1, count2, count3
    10:00, 10:04, 1, 2, 2
    10:05, 10:09, 3, 2, 0

Thanks again...

Alex M
< a l e x _ m 7 4   at   h o t m a i l   dot   c o m >


Quote:
> Alexis,

>   Here is a generic count-by-intervals script.  I hope it helps.

> create function Intervals (




>   StartPeriod datetime primary key,
>   EndPeriod datetime
> ) as begin





>   end
>   return
> end
> go

> select count(OrderID) as OrderCount,
>        StartPeriod as [From],
>        EndPeriod as [To]
>        from northwind..orders right outer join
> dbo.Intervals('1996-01-01','2000-01-01',7776000)
> on OrderDate >= StartPeriod
> and OrderDate < EndPeriod
> group by StartPeriod, EndPeriod

> drop function Intervals

> Steve Kass
> Drew University


> > Hello

> > I have a rather large database (over 600,000 rows) which basically
contains
> > a number of system events that have occurred during the day (we get
about
> > 64,000 events per day, and we keep 10 working days worth of data in the
> > table).

> > The actions are categorised by priority (from 1 to 6) and originator
(from 1
> > to 10, each number is allocated to a sales customer services team).

> > So basically, each row would look like:
> >     TimeStamp, Priority, Originator, EventID, EventStatus

> > I would like to create an SQL statement that will break up the day into
5
> > minute slots (288 per day in total), and count the number of events for
each
> > priority and originator for that 5 minute slot.

> > Right now, I'm trying something like:
> >     SELECT
> >      (SELECT count(*) FROM tblEvents
> >       WHERE Priority=7

> >      ) AS '7',
> >      (SELECT count(*) FROM tblEvents
> >       WHERE Priority=6

> >      ) AS '6',
> >      (and so on)


> > minutes, and the above is inserted into another table. Right now, this
> > process runs as a job once every hour, and appends 12 new rows every
hour
> > (12 5-minute periods per hour).

> > Is there a better way of doing this on the fly? i.e. using a single SQL
> > query to retrieve all the data, rather than have to use a batch process?

> > Thanks

> > Alex M
> > < a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 08:02:40 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement
1)  Stop usiong that silly "tbl-" prefix; you name a table by what it
means LOGICALLY and not how it is stored PHYSICALLY.  Now, if this table
is used to track the history of dingin tables, then the name is fine...

2) TIMESTAMP is a reserved word in SQL and it is vague -- what does it
time stamp?

3) First, let's improve your basic query:


          COUNT(*) as tally_all,
          SUM (CASE WHEN priority = 6
                    THEN 1 ELSE 0 END) AS tally_6,
          SUM (CASE WHEN priority = 7
                    THEN 1 ELSE 0 END) AS tally_7,
          SUM (CASE WHEN priority = 8
                    THEN 1 ELSE 0 END) AS tally_8,
          ...
     FROM Events
    WHERE event_timestamp

This will be a good bit faster than subqueries.

4) You need another table on the side, which you can build on the fly

 CREATE TABLE TimeSlices
 (startdate DATETIME NOT NULL,
  enddate DATETIME NOT NULL,
  CHECK (enddate = startdate + INTERVAL 5 MINUTES),
  PRIMARY KEY (startdate, enddate));

Now do a simple join and group by the intervals:

   SELECT T1.startdate, T1.enddate,
          COUNT(E1.event_id) AS tally_all,
          SUM (CASE WHEN E1.priority = 6
                    THEN 1 ELSE 0 END) AS tally_6,
          SUM (CASE WHEN E1.priority = 7
                    THEN 1 ELSE 0 END) AS tally_7,
          SUM (CASE WHEN E1.priority = 8
                    THEN 1 ELSE 0 END) AS tally_8,
          ...
     FROM Events AS E1, TimeSlices AS T1
    WHERE event_timestamp
          BETWEEN T1.startdate AND T1.enddate
    GROUP BY T1.startdate, T1.enddate;

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 08 Jun 2004 08:54:42 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement
Joe,

Many thanks... works like a charm....


Quote:
> 1)  Stop usiong that silly "tbl-" prefix; you name a table by what it
> means LOGICALLY and not how it is stored PHYSICALLY.  Now, if this table
> is used to track the history of dingin tables, then the name is fine...

Funny how 'tables' are referred to as 'tables' in SQL Enterprise
Manager...and all SQL texts I've read so far... I didnt know you could call
them by any other name... anyway, the main reason I prefix that is to group
all my 'tables' together when show in Enterprise Manager (I know I can sort
by Type, but it's a habit that I'm stuck with). I also heard a rumor that a
table is something with vertical supports and a horizontal surface, and
people use them to put things on...

On a more serious note, a table stores tabular data... and a database is a
collection of tables, is it not?

Quote:

> 2) TIMESTAMP is a reserved word in SQL and it is vague -- what does it
> time stamp?

My bad... in my actual table definition it's [EventTime], not TimeStamp...
it marks the time the event was recorded.

Alex M
< a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 10:23:53 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement


Quote:
> Joe,

> Many thanks... works like a charm....



> > 1)  Stop usiong that silly "tbl-" prefix; you name a table by what it
> > means LOGICALLY and not how it is stored PHYSICALLY.  Now, if this table
> > is used to track the history of dingin tables, then the name is fine...

> Funny how 'tables' are referred to as 'tables' in SQL Enterprise
> Manager...and all SQL texts I've read so far... I didnt know you could
call
> them by any other name... anyway, the main reason I prefix that is to
group
> all my 'tables' together when show in Enterprise Manager (I know I can
sort
> by Type, but it's a habit that I'm stuck with). I also heard a rumor that
a
> table is something with vertical supports and a horizontal surface, and
> people use them to put things on...

> On a more serious note, a table stores tabular data... and a database is a
> collection of tables, is it not?

Actually, a database is a collection of relations, which have tuples and
attributes. According to Chris Date, "table" is a perfectly acceptable
informal alternative for "relation," "row" and "record" are perfectly and
equally acceptable informal alternatives for "tuple", and "column" and
"field" likewise for "attribute." Rather than accept this eminently
reasonable taxonomy, Joe continues to preach The Gospel According to Celko.
Most people simply ignore his rants.

Stu Bloom, MCSD



Tue, 08 Jun 2004 10:54:01 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement
Alexis,

  Simply add Priority to the select and group by clause of your
query and it will give the result normalized:

select EmployeeID, count(OrderID) as OrderCount,
       StartPeriod as [From],
       EndPeriod as [To]
       from northwind..orders right outer join
dbo.Intervals('1996-01-01','2000-01-01',7776000)
on OrderDate >= StartPeriod
and OrderDate < EndPeriod
group by EmployeeID, StartPeriod, EndPeriod

If you then want the counts together in the same row,
you can do this:

select
  StartPeriod as [From],
  EndPeriod as [To],
  count(case when EmployeeID = 1 then 1 end) as Count_1,
  count(case when EmployeeID = 2 then 1 end) as Count_2,
  count(case when EmployeeID = 3 then 1 end) as Count_3,
  count(case when EmployeeID = 4 then 1 end) as Count_4
--the id's go up to 9, but you get the picture
from northwind..orders right outer join
dbo.Intervals('1996-01-01','2000-01-01',7776000)
on OrderDate >= StartPeriod
and OrderDate < EndPeriod
group by StartPeriod, EndPeriod

Steve

Quote:

> Steve,

> Thanks... my script is very similar to this (maybe not as elegant).

> My problem is that I want to return the number of events that happened for
> each priority separately, but in the same script... perhaps an example would
> be in order (very simplified):
> Time    Priority
> 10:00        1
> 10:01        2
> 10:02        3
> 10:03        3
> 10:04        2
> 10:05        1
> 10:06        2
> 10:07        1
> 10:08        1
> 10:09        2

> I would like the SQL query to return something like (for 5 minute
> intervals):
>     start, end, count1, count2, count3
>     10:00, 10:04, 1, 2, 2
>     10:05, 10:09, 3, 2, 0

> Thanks again...

> Alex M
> < a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



> > Alexis,

> >   Here is a generic count-by-intervals script.  I hope it helps.

> > create function Intervals (




> >   StartPeriod datetime primary key,
> >   EndPeriod datetime
> > ) as begin





> >   end
> >   return
> > end
> > go

> > select count(OrderID) as OrderCount,
> >        StartPeriod as [From],
> >        EndPeriod as [To]
> >        from northwind..orders right outer join
> > dbo.Intervals('1996-01-01','2000-01-01',7776000)
> > on OrderDate >= StartPeriod
> > and OrderDate < EndPeriod
> > group by StartPeriod, EndPeriod

> > drop function Intervals

> > Steve Kass
> > Drew University


> > > Hello

> > > I have a rather large database (over 600,000 rows) which basically
> contains
> > > a number of system events that have occurred during the day (we get
> about
> > > 64,000 events per day, and we keep 10 working days worth of data in the
> > > table).

> > > The actions are categorised by priority (from 1 to 6) and originator
> (from 1
> > > to 10, each number is allocated to a sales customer services team).

> > > So basically, each row would look like:
> > >     TimeStamp, Priority, Originator, EventID, EventStatus

> > > I would like to create an SQL statement that will break up the day into
> 5
> > > minute slots (288 per day in total), and count the number of events for
> each
> > > priority and originator for that 5 minute slot.

> > > Right now, I'm trying something like:
> > >     SELECT
> > >      (SELECT count(*) FROM tblEvents
> > >       WHERE Priority=7

> > >      ) AS '7',
> > >      (SELECT count(*) FROM tblEvents
> > >       WHERE Priority=6

> > >      ) AS '6',
> > >      (and so on)


> > > minutes, and the above is inserted into another table. Right now, this
> > > process runs as a job once every hour, and appends 12 new rows every
> hour
> > > (12 5-minute periods per hour).

> > > Is there a better way of doing this on the fly? i.e. using a single SQL
> > > query to retrieve all the data, rather than have to use a batch process?

> > > Thanks

> > > Alex M
> > > < a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 11:31:00 GMT
 Interesting problem??? multiple COUNT(*) in one SQL statement
Alexis,

   Joe's query will not count things correctly.  Any event that
occurs exactly at a start or end time will be counted twice.

Steve

Quote:

> Joe,

> Many thanks... works like a charm....



> > 1)  Stop usiong that silly "tbl-" prefix; you name a table by what it
> > means LOGICALLY and not how it is stored PHYSICALLY.  Now, if this table
> > is used to track the history of dingin tables, then the name is fine...

> Funny how 'tables' are referred to as 'tables' in SQL Enterprise
> Manager...and all SQL texts I've read so far... I didnt know you could call
> them by any other name... anyway, the main reason I prefix that is to group
> all my 'tables' together when show in Enterprise Manager (I know I can sort
> by Type, but it's a habit that I'm stuck with). I also heard a rumor that a
> table is something with vertical supports and a horizontal surface, and
> people use them to put things on...

> On a more serious note, a table stores tabular data... and a database is a
> collection of tables, is it not?

> > 2) TIMESTAMP is a reserved word in SQL and it is vague -- what does it
> > time stamp?
> My bad... in my actual table definition it's [EventTime], not TimeStamp...
> it marks the time the event was recorded.

> Alex M
> < a l e x _ m 7 4   at   h o t m a i l   dot   c o m >



Tue, 08 Jun 2004 11:31:10 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. SQL: Count Multiple Tables in One Statement

2. Multiple Count in one statement...

3. multiple connections vs multiple statements on one connection

4. Interesting problem with SQL Statement

5. Interesting problem in SQL statement

6. Interesting SQL Statement Problem...

7. Condense multiple SQL Statements into one

8. sql question--multiple joiins in one statement

9. insert to multiple records in one SQL statement

10. executing multiple SQL statements in one call

11. Execute multiple sql in one statement

12. One SQL statement to update multiple tables?


 
Powered by phpBB® Forum Software