Query with aggregate function as a column : Aggregate function « Aggregate Functions « SQL Server / T-SQL Tutorial






4> IF OBJECT_ID('dbo.Sessions') IS NOT NULL
5>   DROP TABLE dbo.Sessions;
6> GO
1>
2> CREATE TABLE dbo.Sessions
3> (
4>   keycol    INT         NOT NULL IDENTITY PRIMARY KEY,
5>   app       VARCHAR(10) NOT NULL,
6>   usr       VARCHAR(10) NOT NULL,
7>   starttime DATETIME    NOT NULL,
8>   endtime   DATETIME    NOT NULL,
9>   CHECK(endtime > starttime)
10> );
11> GO
1>
2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 08:30', '20060212 10:30');
3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 08:30', '20060212 08:45');
4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:00', '20060212 09:30');
5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 09:15', '20060212 10:30');
6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:15', '20060212 09:30');
7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 10:30', '20060212 14:30');
8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 10:45', '20060212 11:30');
9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 11:00', '20060212 12:30');
10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 08:30', '20060212 08:45');
11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 09:00', '20060212 09:30');
12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 11:45', '20060212 12:00');
13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 12:30', '20060212 14:00');
14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 12:45', '20060212 13:30');
15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 13:00', '20060212 14:00');
16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 14:00', '20060212 16:30');
17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 15:30', '20060212 17:00');
18> GO
1>
2>
3> SELECT app,
4>   (SELECT COUNT(*) FROM dbo.Sessions AS C
5>    WHERE ts >= starttime
6>      AND ts < endtime) AS cnt
7> FROM (SELECT DISTINCT app, starttime AS ts
8>      FROM dbo.Sessions) AS T;
9>
10> GO
app        cnt
---------- -----------
app1                 3
app1                 3
app1                 5
app1                 1
app1                 2
app1                 3
app2                 3
app2                 3
app2                 3
app2                 2
app2                 3
app2                 4
app2                 2
app2                 2
1>
2> drop table sessions;
3> GO
1>








9.1.Aggregate function
9.1.1.Aggregate functions are applied to a group of data values from a column.
9.1.2.Using calculations within aggregate functions.
9.1.3.Query with aggregate function as a column
9.1.4.Aggregate function in sub query
9.1.5.Using Aliase with aggregate function