Query Help

Question

Query Help

sbauer

SSCommitted

Points: 1966
More actions
September 23, 2004 at 3:28 pm

#161537

I have two tables that hold the data, one is the base table and the other a history table. I need to get a count of both table and tried to use the union, but still don't have the output I need.
QUERY
select count(prior_ins_name) as 'Count #', c.param_decode as 'Carrier Name'
from auto..general g(nolock), auto.dbo.code_decode_parms c(nolock)
where g.prior_ins_name = c.param_cd
and param_type = 'Carrier'
group by param_decode, prior_ins_name
union
select count(prior_ins_name) as 'Count #', c.param_decode as 'Carrier Name'
from autohistory..general_history g(nolock), auto.dbo.code_decode_parms c(nolock)
where g.prior_ins_name = c.param_cd
and param_type = 'Carrier'
group by param_decode, prior_ins_name
order by count(prior_ins_name)desc
CURRENT OUTPUT
Count #     Carrier Name
----------- --------------------------------------------------------------------------------------------------------------------------------
1154778     Other
842048      State Farm Fire & Casualty
723328      Other
629687      Allstate Insurance Group
501633      GEICO
375793      Allstate Insurance Group
317615      GEICO
270531      State Farm Fire & Casualty
235022      Nationwide Insurance
153567      American Family Insurance Group
144218      AIG
141453      Nationwide Insurance
139703      USAA Group
102763      Liberty Mutual
96179       American Family Insurance Group
93223       Select...
93180       Travelers Insurance Group
89503       Safeco Insurance Group
86248       State Farm
83499       AIG
83143       Mercury General Group
74697       Farmers Insurance Group
73067       Twentieth Century Insurance Group
71018       Hartford Fire & Casualty Group
69571       Travelers Insurance Group

OUTPUT NEEDED - I need to total all both counts per carrier
i.e.
1878106    Others (total of general and general_history)
1112579    State Farm Fire & Casualty
Any help would be appreciated.
Thanks
Susan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

Susan you're problem seems to be that you need group the output obtained from the union. This is not exactly an elegant solution but should work. All have done is take your relation produced by your query and used it as a derived table in the from clause and then grouped it's results.

select sum([Count #]), [Carrier Name]

FROM (select count(prior_ins_name) as 'Count #', c.param_decode as 'Carrier Name'

from auto..general g(nolock), auto.dbo.code_decode_parms c(nolock)

where g.prior_ins_name = c.param_cd

and param_type = 'Carrier'

group by param_decode, prior_ins_name

union

select count(prior_ins_name) as 'Count #', c.param_decode as 'Carrier Name'

from autohistory..general_history g(nolock), auto.dbo.code_decode_parms c(nolock)

where g.prior_ins_name = c.param_cd

and param_type = 'Carrier'

group by param_decode, prior_ins_name) a

GROUP BY [Carrier Name]

ORDER BY SUM([COUNT #]) DESC

This isn't pretty but should work

HtH

David

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 2

do non-clustered index on carrier name it should work

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 3

ur answer is great i never thought of union query throwing a resultset and then grouping it

simply great

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

sbauer SSCommitted Points: 1966 More actions · Answer 4

Thanks for the help.

It's exactly what I needed. You guys ROCK!!