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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply