CODEWITHSUNDEEP
mysqlsql
Dani
Dani

Reputation: 41

SQL - Counting from 2 or more Columns

I am somehow new to SQL, and I have a Election Application. I have done 80% of it and now stuck at counting votes from 2 or more Columns.

Example Table:

|**Senator_1** | **Senator_2** | **Senator_3**|
----------------------------------------------
   George      |    Carl       |   Anthony
   Carl        |    Tony       |   Stark
   Anthony     |    George     |   Tony
   Anthony     |    George     |   Stark

I would like to have this kind of result.

|**Candidate_Name** | **Vote_Count**|
-------------------------------------
   George           |   3
   Anthony          |   3
   Carl             |   2
   Stark            |   2
   Tony             |   2

I really don't have any idea of what query I am going to use. Any ideas of solving this?

By the way, for the confusion and all the arguments that started here, I am going to explain:

I wanted to be straight to my problem that's why I just posted a sample table. I have a table for the Voters, Candidates and the Votes. All tables have its ID and such, so I'm pretty sure it's normalized.

Upvotes: 2

Views: 169

Answers (2)

Taryn
Taryn

Reputation: 247710

The main issue that you have is your table is not normalized. I would strongly advise that you fix your current table structure. A possible new table structure would be:

/* Table for unique voters */
CREATE TABLE voters (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL 
) ENGINE=InnoDB;

/* Table for unique candidates */
CREATE TABLE candidates (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

/* Many-to-many table storing votes by voters for candidates */
CREATE TABLE votes (
    voter_id INT UNSIGNED NOT NULL,
    candidate_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (voter_id, candidate_id),
    CONSTRAINT FOREIGN KEY (voter_id) REFERENCES voters (id),
    CONSTRAINT FOREIGN KEY (candidate_id) REFERENCES candidates (id)
) ENGINE=InnoDB;

/* Populate data */
INSERT INTO voters (name)
VALUES ('Voter 1'), ('Voter 2'), ('Voter 3'), ('Voter 4');

INSERT INTO candidates (name)
VALUES ('George'), ('Carl'), ('Anthony'), ('Tony'), ('Stark');

INSERT INTO votes (voter_id, candidate_id)
VALUES (1,1), (1,2), (1,3),
       (2,2), (2,4), (2,5),
       (3,3), (3,1), (3,4),
       (4,3), (4,1), (4,5);

Then you could easily get a result by joining the two tables:

/* query showing all voters and the candidates they voted for */
SELECT voters.name, candidates.name
FROM votes
    INNER JOIN voters on votes.voter_id = voters.id
    INNER JOIN candidates ON votes.candidate_id = candidates.id;

/* Finally, a query showing votes per candidate */
SELECT candidates.name, COUNT(*) AS votes
FROM votes
    INNER JOIN candidates ON votes.candidate_id = candidates.id
GROUP BY candidates.id;

See SQL Fiddle with Demo

However, if you cannot alter the design of the table, then you can get the result by unpivoting the data that you have in multiple columns. You can use a UNION ALL to unpivot the multiple columns into rows to get the count:

select name, count(*) TotalVotes
from
(
  select senator_1 name
  from yt
  union all
  select senator_2 name
  from yt
  union all
  select senator_3 name
  from yt
) d
group by name
order by totalVotes desc;

See SQL Fiddle with Demo

Upvotes: 11

siddharth
siddharth

Reputation: 688

I think you are looking to count the total no. of occurrence of each name in different columns. Based on this, I think something like below might help -

select senator, sum(cnt) as 'count' from (
select senator_1 as 'senator', count(1) 'cnt' from election_table group by senator_1
union all
select senator_2 as 'senator', count(1) 'cnt' from election_table group by senator_2
union all
select senator_3 as 'senator', count(1) 'cnt' from election_table group by senator_3
) x group by x.senator

Upvotes: 0

Related Questions