1

Table "tblvotes"

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
candidateid int(11) NO MUL NULL
districtid int(11) NO NULL
daterecorded datetime NO current_timestamp()

Table "tblcandidate":

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
voterid int(11) NO MUL NULL
partyid int(11) NO MUL NULL
candidatepositionid int(11) NO MUL NULL

Table "tbldistricts":

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
district_short varchar(8) NO NULL
district_name varchar(100) NO NULL
district_aun varchar(10) NO NULL
district_propVal tinyint(4) NO 1

Table "tblvoterlist":

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
idno varchar(15) YES NULL
lastname varchar(30) NO NULL
firstname varchar(30) NO NULL
middlename varchar(30) NO NULL
districtid int(5) YES MUL NULL
image varchar(30) NO NULL
votingcode varchar(15) YES UNI NULL
votestatus char(1) YES NULL
yearlevelid int(12) YES MUL NULL 
SELECT concat_ws(",", tvl.lastname, tvl.firstname) as candidate, td.district_name as district,  count(tv.candidateid) 
FROM tblvotes tv
JOIN tblcandidate tc on tv.candidateid = tc.id
JOIN tbldistricts td on tv.districtid = td.id
JOIN tblvoterlist tvl on tc.voterid = tvl.id

Also tried to group by tv.districtid and I am not sure where the COUNT should be.

unfortunately, I really am not understanding how use the keys to get the desired result.

As an example the desired result is:

Candidate1 Canddiate2 Candidate3 Candidate4
District1 5 2 1 0
District2 0 4 2 1
District3 6 2 3 2

I hope this makes sense.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Lin1
  • 11
  • 3
  • If you want each candidate in a different column, you need to [pivot](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql?noredirect=1&lq=1) – Barmar Jan 25 '23 at 22:21
  • Try this and see if this is what you want.. SELECT concat_ws(",", tvl.lastname, tvl.firstname) as candidate, td.district_name as district, COUNT(tv.candidateid) as votes FROM tblvotes tv JOIN tblcandidate tc on tv.candidateid = tc.id JOIN tbldistricts td on tv.districtid = td.id JOIN tblvoterlist tvl on tc.voterid = tvl.id GROUP BY td.district_name, tvl.lastname, tvl.firstname ORDER BY td.district_name, votes DESC – Gihan Jan 25 '23 at 23:00
  • Thank you Barmar, Unless I am misunderstanding, I just need to figure out how to get the totals for each candidate by district from the four tables. I will put the results in an array and then produce a table or excel workbook or just print it out. – Lin1 Jan 25 '23 at 23:04
  • Thank you Gihan, I think your solution will work. I appreciate it! – Lin1 Jan 25 '23 at 23:18

0 Answers0