Add up all the votes for "3rd party candidates" (libertarians, socialists and independents). If these are combined into one "candidate" per race, which races would the 3rd party have won?
CREATE TABLE Vote(
Vote_ID int,
Voter_ID int,
Candidate_ID int,
Title varchar(50),
PRIMARY KEY(Vote_ID)
);
CREATE TABLE Voter(
Voter_ID int,
First_Name varchar (50),
Mid_Name varchar(50),
Last_Name varchar(50),
Street varchar(45),
Zipcode int,
Party varchar(20),
Registered int,
PRIMARY KEY(Voter_ID)
);
CREATE TABLE Candidate(
Candidate_ID int,
First_Name varchar(50),
Mid_Name varchar(50),
Last_Name varchar(50),
Street varchar(45),
Zipcode int,
Party varchar(20),
Local_PosID int,
State_PosID int,
National_PosID int,
PRIMARY KEY(Candidate_ID)
);
CREATE TABLE National_Position (
National_PosID int,
Title varchar(40),
PRIMARY KEY (National_PosID)
);
CREATE TABLE State_Position (
State_PosID int,
Title varchar(40),
State_Name varchar(2),
Congressional_Districts int,
PRIMARY KEY (State_PosID)
);
CREATE TABLE Local_Position (
Local_PosID int,
Title varchar(40),
City varchar(40),
PRIMARY KEY (Local_PosID)
);
I assume that it needs to SUM function incorporated with a nest select statements but just can't think of way.
SELECT V.Title, count(*)
FROM Vote V, Candidate C
Where C.Party IN ( ‘LIB’, ‘SOC‘, ‘IND‘) >= C.Party IN (‘REP‘, ‘DEM‘) AND V.Candidate_ID = C.Candidate_ID
Group By V.Title, count(*);
