counting query with grouping / Singleton table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

counting query with grouping / Singleton table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-21 : 11:25:11

Gustavo Lacerda writes "Given the table:

TABLE Message:
message_id day source
103 01 BLC
104 01 BLC
105 01 TEL
106 02 FAX
107 03 <NULL>
108 05 TEL
109 05 <NULL>
110 07 TEL
111 08 BLC
112 10 BLC

I would like to create a query that will return the following table:

TABLE 2:

day  cBLC cTEL cFAX

01      2    1    0
02      0    0    1
03      0    0    0
04      0    0    0
05      0    1    0
06      0    0    0
07      0    1    0
08      1    0    0
09      0    0    0
10      1    0    0
TOTAL   4    3    1

The first row here says that there are 2 BLC records and 1 TEL record in day 01. The second row says that there was just one message on day 02, and that it was by fax... and so on.

It's easy to get the total cBLC, cTEL, cFAX:

SELECT (SELECT COUNT(*)
FROM Message
WHERE source = 'BLC') AS cBLC,
(SELECT COUNT(*)
FROM Message
WHERE source = 'TEL') AS cTEL,
(SELECT COUNT(*)
FROM Message
WHERE source = 'FAX') AS cFAX
FROM Message

but (1) this is very wasteful, as I only want these results once. Is there some kind of Singleton table in SQL? If I could replace the last "Message" with such a table, I would get the same answers using only one row. It feels pretty kludgy too.

(2) How do you get the results table by day, as shown in TABLE 2?

What I tried was giving the outer "Message" table a different identifier from the inner ones, and adding the condition that the outer table's day be the same as the inner table's day inside each inner SELECT. Finally, group by day.

This seems terribly kludgy and inefficient... and it doesn't work either. Can you propose a solution?

Thanks.

Gustavo"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources