distinct records

Re: distinct records

On 29 Sep 2005 00:15:24 -0700, "kevcar40" <kevcar40@btint ernet.com>
wrote:
[color=blue]
>i have a query which is the result of 4 queries being joined
>query 1
>reason count_of_reason
>query 2
>reason count_of_reason
>query 3
>reason count_of_reason
>query 4
>reason count_of_reason
>the four queries are joined on reason
>so i end up with
>reason, count_of_reason _1, count_of_reason _2, count_of_reason _3,
>count_of_reaso n_4,
>i end up with reasons being duplicted
>reason reason1 ,count_1, count_2, count_3, count_4,
>test 1 8
>try 5
>test 6
>
>i have tried using the distinct on the reason field but i end up with
>dupliction because the count fields are grouped
>how can i get unique reacored the returns
>
>reason reason1 ,count_1, count_2, count_3, count_4,
>test 1 8 6
>try 5[/color]

Yes, but I'm not sure it is what you want. What you asked for is:

TRANSFORM Sum(e.count_of_ reason) AS [The Value]
SELECT e.reason
FROM [select "count_of_reaso n_1" as one,a.reason,a. count_of_reason
from query_1 a union all select "count_of_reaso n_2" as
one,b.reason,b. count_of_reason from query_2 b union all select
"count_of_reaso n_3" as one,c.reason,co unt_of_reason from query_3 c
UNION ALL select "count_of_reaso n_4" as one,d.reason,d. count_of_reason
from query_4 d]. e
GROUP BY e.reason
PIVOT e.one;

Something tells me you are really after:

SELECT e.reason, Sum(e.SumCounto fReason)
FROM [select a.reason,sum(a. count_of_reason ) as SumCountofReaso n from
query_1 a group by reason union all select
b.reason,sum(b. count_of_reason ) as SumCountofReaso n from query_2 b
group by reason union all select c.reason,sum(c. count_of_reason ) as
SumCountofReaso n from query_3 c group by reason UNION ALL select
d.reason,sum(d. count_of_reason ) as SumCountofReaso n from query_4 d
group by reason]. AS e
GROUP BY reason;

Don't have time for an explanation, so parse it out, one bit at a
time.

If you think something doesn't work, please post the SQL that you end
up using that doesn't work.

mike

Re: distinct records

Ok Will have a go thanks
kevin