Can you create a new field in a rollup summary or does it require a nested query?

Question

The result will be a table where the original table contents are 450,000 rows (or records) of precinct election results for each statewide race in Texas.

Here's my query the way it is:

select 
 UPPER(county) as TXCounty, Name as Presidential_Candidate, 
 sum(votes) as Votes
from ['2020_General_Election_Returns$']
where name in ('Candidate 1', 'Candidate 2')
group by county, Office, Name
order by County, Office, name

The query results show the county repeated twice, one for candidate 1, the other for candidate 2. Meaning, out of 254 Texas counties, it would return 508 rows instead of 254 because it's duplicating the same county to get the values for candidate 2. Is there a way to create a query where there's two extra columns for candidate 2 and the votes they receive? I'm thinking a nested query would do but I'm not proficient in that area yet. It's a lot cleaner than having the county name repeat for candidate 2.

Answer 1

If you always have exactly two candidates you can use conditional aggregation

select 
  UPPER(county) as TXCounty,
  min(case when name = 'Candidate 1' then Name end) as Presidential_Candidate1,
  sum(case when name = 'Candidate 1' then votes end) as Votes1,
  min(case when name = 'Candidate 2' then Name end) as Presidential_Candidate2,
  sum(case when name = 'Candidate 2' then votes end) as Votes2
from ['2020_General_Election_Returns$']
where name in ('Candidate 1', 'Candidate 2')
group by
  county
order by
  county;

Admittedly min(case when name = 'Candidate 1' then Name end) could just as well be 'Candidate 1' but it also allows you to use row-numbering

select 
  UPPER(county) as TXCounty,
  min(case when rn = 1 then Name end) as Presidential_Candidate1,
  sum(case when rn = 1 then votes end) as Votes1,
  min(case when rn = 2 then Name end) as Presidential_Candidate2,
  sum(case when rn = 2 then votes end) as Votes2
from (
    select *,
      row_number() over (partition by county order by Name) as rn
    from ['2020_General_Election_Returns$']
    where name in ('Candidate 1', 'Candidate 2')
) t
group by
  county
order by
  county;