February 25, 2005 at 1:56 pm
I have three tables (Tom, Dick and Harry)
|
|
|
I want to obtain a result with a count of laps for each name.
| Name | Laps |
|---|---|
| A | 1 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
The tables for Tom, Dick and Harry are quite large, so I would prefer not to iterate through each row. Can someone advise me on how to construct the query?
Thanks in advance.
February 25, 2005 at 2:11 pm
Select Name, Sum(Laps) As TotalLaps
From
(
Select Name, Laps
From Tom
Union All
Select Name, Laps
From Dick
Union All
Select Name, Laps
From Harry
) vtable
February 25, 2005 at 3:04 pm
Thanks for the quick reply. I tried your suggestion, but I received an error:
"Column 'vtable.Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Any Suggestions?
February 25, 2005 at 3:08 pm
Select Name, Sum(Laps) As TotalLaps
From
(
Select Name, Laps
From Tom
Union All
Select Name, Laps
From Dick
Union All
Select Name, Laps
From Harry
) vtable
GROUP BY vtable.Name
February 25, 2005 at 3:11 pm
Doh. Thanks Remi.
Cerebral cortex system error: Please install more caffeine and try the operation again.
February 25, 2005 at 3:21 pm
worked like a charm.
Big Thanks to Remi and PW.
February 25, 2005 at 3:30 pm
Everybody needs a little cefeine at this hour on a friday.
HTH
Brain shut off sequence has begun. Happy week-end.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply