>I have a table as folows:
>
>Inv No. Continent Country INV_VAL Town Units_SOLD
>
>1 EUROPE UK 100 LONDON 10
>1 EUROPE UK 100 MANCHESTER 3
>
>2 EUROPE UK 500 LIVERPOOL 1
>2 EUROPE UK 500 BIRMINGHAM 4
>
>
>3 ASIA CHINA 300 BEJING 23
>3 ASIA CHINA 300 SHANGHAI 12
>
>
>I want to do a sql select which totals and groups by Continent/Country but ensure
> that the INV_VAL column is also totalled correctly.
>If I do a sum(Inv_Val) I will get 1200 for Europe instead of 600 (100 + 500)
>The Inv_Val already has the total INV_VAL for each invoice.
>
>The resuting table that I need is
>Continent Country CTRY_SALES POP_TOTAL
>EUROPE UK 600 18
>ASIA CHINA 300 35
>
>I have'nt been able to figure out a way to do this with one Sql Seelct command
> and am wondering if its possible.
>Tia,
>Gerard
select S.Continent, S.Country, sum(T.Total) as Ctry_Sales, sum(S.Units_Sold) as Pop_Total
from Sales inner join
(select Continent, Country, max(INV_Val) as Total from Sales group by 1,2) T
on S.Continent = T.Continent and S.Country = T.Country
group by S.Continent, S.Country
If it's not broken, fix it until it is.
My Blog