Grouping query to produce multiple sum columns

[danielm28]

I maintain an awards show application.
The way it works is that a company nominates an individual for an award.
Once that individual is nominated, they are contacted to find out whether or not they are interested in
entering the contest for the award. If so, they enter and wait to see whether or not they've won.

I have a view that groups Nominating companies that have similar characteristics.
Here's a sample of the v_CompanyGroups results:

nominatorID companyName country companyGroup
1 Blue US Blue
2 Dark Blue US Blue
3 Dark Green Germany Green
4 Yellow US Yellow
5 Goldon Yellow Germany Yellow
6 Light Yellow Ireland Yellow

I need to write a query that produces the following report:

CompanyGroup Nominating Co. Country Nominated Entered Won

Blue US 10 1 0
Green Germany 10 8 2
Yellow US 4 1 1
Germany 3 0 0
Ireland 1 1 1

To find out who a company has nominated --> v_CompanyGroups.nominatorID = jNominee.nominatorID
To find out which nominees have entered that have been nominated by these companies --> jNominee.nomineeID = application.appNumber
To find out which nominees have won --> application.appNumber = winner.appNumber

What would be the query for the CompanyGroup report above?

The reports requires that CompanyGroup only be shown, but it still needs to
breakdown totals for each companyName country.

Thanks in advance

[danielm28]

I have figured out the query for this.

It is

CREATE PROCEDURE dbo.companyGroupNomEntWon
AS
set nocount on
exec('select nominatorid,count(*) as NumOfNominees
into #NumOfNominees
from user1071209.nominee n
group by nominatorid
order by nominatorid

select NOminatorID,count(*) as NumOfApps
into #NumOfApps
from user1071209.jnominee n join user1071209.application a on n.nomineeid=substring(a.appNumber,4,4)
group by NominatorID

select nominatorId,count(*) as numOfwinners
into #NumOfWinners
from user1071209.jnominee n join user1071209.application a on n.nomineeid=substring(a.appNumber,4,4) join dbo.winner w on a.appnumber=w.appnumber
group by NominatorID

select n.nominatorid,numofnominees,numofapps,numofwinners
into #joined
from #numofnominees n left join #numofapps a on n.nominatorID=a.nominatorID left join #numofwinners w on n.nominatorid=w.nominatorid

select companygroup as company,country,sum(isnull(numofnominees,0)) as nominated,sum(isnull(numofapps,0)) as entered,sum(isnull(numofwinners,0)) as won
from #joined j right join [user1071209].[v_companygroups] g on j.nominatorid=g.nominatorid
group by companygroup,country
order by companygroup,country

drop table #numofnominees
drop table #numofapps
drop table #numofwinners
drop table #joined ')
GO

[Rufal]

Hi, This is quite easy to do in a single select.

The trick is to use decode with sum/group by's..

so, ( I'm going to generalize this)
You have a simple/complex select that gives you output such as :
/cat/ /num/
blue 1
red 1
yellow 3
black 10
blue 4
yellow 1
etc...

you can wrap them up with
select
sum(decode(cat,'blue',num,0)) as blue,
sum(decode(cat,'red',num,0)) as red,
sum (decode(cat,'yellow',num,0)) as yellow,
sum(decode(cat,'black',num,0)) as black
from /complex query/
group by cat;

Use CASE if your RDBMS does'nt support DECODE

the thing here is that by using decode/case you will only sum (or count) up depending on field "CAT" being the trigger (the first).

the only drawback by using this method is that you need to know beforehand how many columns you want the result to produce.

so this example would produce
/blue/ /yellow/ /red/ /black/
5 4 1 10

only a single result. (and if you want aggregation you get that with multiple rows i.e. add another group by item: for instance STATE
STATE1 blue=4 yellow=1 ....
STATE2 blue=5.......
Quite clever and handy)

Regards
Oli