SEO

#1 (**permalink**) 02-26-2008, 10:02 AM

Hi,

I have the following table:

NAME varchar(10)
V1 integer
V2 integer
V3 integer

Values in the table are like:
NAME V1 V2 V3
---------- ----------- ----------- -----------
a 11 16 11
b 20 20 12
c 25 35 13
c 25 35 14
c 25 35 15

I run the SQL:

select name, v2-v1 as difference
from road.aa
where v3>10
group by name, v1, v2

The group by clause is used to avoid retrieving duplicate values
(V2-V1 can have duplicate records).

The result is:

NAME DIFFERENCE
---------- -----------
a 5
b 0
c 10

I now need to find the total for the column DIFFERENCE. How can I do
this?

If use GROUP BY GROUPING SETS and put a SUM(V2-V1) it calculates the
SUM of the duplicate recrods as well (which I want to avoid). Please
advice.

Regards,
Ashish

#2 (**permalink**) 02-26-2008, 10:02 AM

Ashish <ashishhcl@yahoo.com> wrote:

> Hi,
>
> I have the following table:
>
> NAME varchar(10)
> V1 integer
> V2 integer
> V3 integer
>
> Values in the table are like:
> NAME V1 V2 V3
> ---------- ----------- ----------- -----------
> a 11 16 11
> b 20 20 12
> c 25 35 13
> c 25 35 14
> c 25 35 15
>
> I run the SQL:
>
> select name, v2-v1 as difference
> from road.aa
> where v3>10
> group by name, v1, v2
>
> The group by clause is used to avoid retrieving duplicate values
> (V2-V1 can have duplicate records).
>
> The result is:
>
> NAME DIFFERENCE
> ---------- -----------
> a 5
> b 0
> c 10
>
> I now need to find the total for the column DIFFERENCE. How can I do
> this?

SELECT SUM(difference)
FROM ( select name, v2-v1 as difference
from road.aa
where v3>10
group by name, v1, v2 ) AS n

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Finding grand total