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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-07 : 16:44:34
|
Gary writes "Last week Rob Volk helped me solve a problem that I never thought I would find a solution to.
Rob if your out there "Thanks Again!" If you are up to the challenge I have one more question?
Is it possible to use the COMPUTE clause to give me a total for the three Terminals?
Here is the original Query.
Select delay1, Count(*) as Total,
Sum(Case gates.terminal WHEN "A" Then 1 else 0 end) AS TerminalA,
Sum(Case gates.terminal WHEN "B" Then 1 else 0 end) AS TerminalB,
Sum(Case gates.terminal WHEN "C" Then 1 ELSE 0 END) AS TerminalC
FROM dbo.Flights INNER JOIN dbo.Gates ON dbo.Flights.DepGate=dbo.Gates.Gate AND dbo.Flights.ActDepCity=dbo.Gates.Station
WHERE datediff(dd,getdate(), actdate) = -15 and Flight <3000 and ActDepCity = 'DFW' and Minutes1 >05
GROUP BY delay1
ORDER BY delay1
Here are the results:
delay1 Total TerminalA TerminalB TerminalC
------ ----------- ----------- ----------- -----------
01 2 1 0 1
01B 1 0 0 1
05 1 1 0 0
07 1 0 1 0
09 2 0 0 2
11 1 0 1 0
12B 1 1 0 0
14 1 0 0 1
15B 2 1 0 1
27B 3 0 0 3
28 4 3 0 1
29A 3 0 1 2
46 23 6 3 14
50 1 1 0 0
50M 2 2 0 0
54 2 0 0 2
63 15 8 1 6
66A 2 1 0 1
72A 1 0 0 1
75B 1 1 0 0
75H 1 1 0 0
80F 1 0 0 1
80G 1 0 1 0
80H 1 1 0 0
90C 12 3 1 8
91 1 0 0 1
91T 1 1 0 0
94X 1 1 0 0
I have tried using the COMPUTE as follows:
COMPUTE SUM (delay1)
But I get this response which I understand.
The sum or average aggregate operation cannot take a char data type as an argument.
I have tried COMPUTE SUM (TerminalA)
But get this response.
Invalid column name 'TerminalA'.
I have also tried COMPUTE SUM (BY TERMINALA)
But get this response.
Incorrect syntax near the keyword 'BY'.
What is the key to get the totals of the columns that are created in the CASE statement?
Thanks again
Gary" |
|
|
|
|
|
COMPUTE in a CASE STATEMENT