|
|
Posted by Hugo Kornelis on 12/22/05 00:21
On 21 Dec 2005 13:51:38 -0800, Ryan wrote:
>Hello,
>
>I am trying to create a view that shows the following
>
>Field1: Sum of Amounts from Table A
>Field2: Count of Amounts from Table A
>
>Field3: Sum of of Amounts from Table B
>Field4: Count of Amounts from Table B
>.
>.
>.
>Field3: Sum of of Amounts from Table H
>Field4: Count of Amounts from Table H
>.
>.
>.
>Things are a bit more complex but this is the gist.
>
>I am using SQL 2000.
>
>I know how to do this pretty easily using a stored procedure. But how
>can I do it in a view? A SQL server won't meet my needs in this
>situation.
Hi Ryan,
This can be done in a single query. You can of course encapsulate that
in a view, stored procedure, or whatever.
To prevent double table-scanning, here's a query that will scan each
table only once:
SELECT Field1, Field2, Field3, ..., Field16
FROM (SELECT SUM(Amount) AS Field1, COUNT(Amount) AS Field2
FROM TableA) AS A
CROSS JOIN (SELECT SUM(Amount) AS Field3, COUNT(Amount) AS Field4
FROM TableB) AS B
.....
CROSS JOIN (SELECT SUM(Amount) AS Field15, COUNT(Amount) AS Field16
FROM TableH) AS H
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|