Im creating a routine to give me a output like
| Row | Voters | Households |
| Total | 15235 | 7250 |
| Poll | NULL | NULL |
| <70% | 5000 | 2500 |
| <80% | 3500 | 2235 |
.....
| AV | NULL | NULL |
| <70% | 300 | 150 |
.....
| PAV | NULL | NULL |
| <70% | 1894 | 1201 |
All goes from <70% to <100%
I have a routine code
CREATE PROCEDURE `district_Propensity_counts`(IN target INT)
COMMENT 'build demographic table for district'
BEGIN
DROP TABLE IF EXISTS Temp;
CREATE TEMPORARY TABLE Temp (
Row VARCHAR(25) DEFAULT NULL,
Col VARCHAR(15) DEFAULT NULL,
Data MEDIUMINT(12) UNSIGNED DEFAULT 0,
Seq TINYINT(2) UNSIGNED DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Temp
SELECT 'Total' AS Row,
'Voters' AS Col,
COUNT(*) AS Data,
1 as Seq
FROM VoterProfile p
INNER JOIN DistrictPrecinct d
ON d.precinct_id=p.precinct_code
WHERE district_id=target
UNION
SELECT 'Total' AS Row,
'Households' AS Col,
COUNT(HHKey) AS Data,
1 as Seq
FROM (SELECT HHKey
FROM VoterProfile p
INNER JOIN DistrictPrecinct d
ON d.precinct_id=p.precinct_code
WHERE district_id=target
GROUP BY HHKey) AS A
UNION
SELECT 'Poll' AS Row,
NULL AS Col, NULL AS Data, 2 AS Seq
UNION
(
SELECT r.Row
, 'Voters' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 3 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.poll_propensity >= 0.70 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c70
, SUM(vp.poll_propensity >= 0.80 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c80
, SUM(vp.poll_propensity >= 0.90 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c90
, SUM(vp.poll_propensity >= 1 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c100
, COUNT(p.voter_id) AS count_voter_id
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
) d
ORDER BY r.Row)
UNION(
SELECT r.Row
, 'Households' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 3 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.poll_propensity >= 0.70 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c70
, SUM(vp.poll_propensity >= 0.80 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c80
, SUM(vp.poll_propensity >= 0.90 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c90
, SUM(vp.poll_propensity >= 1 AND p.perm_absentee = 'N' AND vp.mail_propensity = 0) AS c100
, COUNT(p.HHKey) AS count_HHKey
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
GROUP BY HHKey
) d
ORDER BY r.Row)
UNION
SELECT 'AV' AS Row,
NULL AS Col, NULL AS Data, 4 AS Seq
UNION(
SELECT r.Row
, 'Voters' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 5 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.mail_propensity >= 0.70 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c70
, SUM(vp.mail_propensity >= 0.80 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c80
, SUM(vp.mail_propensity >= 0.90 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c90
, SUM(vp.mail_propensity >= 1 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c100
, COUNT(p.voter_id) AS count_voter_id
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
) d
ORDER BY r.Row)
UNION(
SELECT r.Row
, 'Households' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 5 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.mail_propensity >= 0.70 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c70
, SUM(vp.mail_propensity >= 0.80 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c80
, SUM(vp.mail_propensity >= 0.90 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c90
, SUM(vp.mail_propensity >= 1 AND p.perm_absentee = 'N' AND vp.poll_propensity = 0) AS c100
, COUNT(p.HHKey) AS count_HHKey
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
GROUP BY HHKey
) d
ORDER BY r.Row)
UNION
SELECT 'PAV' AS Row,
NULL AS Col, NULL AS Data, 6 AS Seq
UNION(
SELECT r.Row
, 'Voters' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 7 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.mail_propensity >= 0.70 AND p.perm_absentee = 'Y') AS c70
, SUM(vp.mail_propensity >= 0.80 AND p.perm_absentee = 'Y') AS c80
, SUM(vp.mail_propensity >= 0.90 AND p.perm_absentee = 'Y') AS c90
, SUM(vp.mail_propensity >= 1 AND p.perm_absentee = 'Y') AS c100
, COUNT(p.voter_id) AS count_voter_id
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
) d
ORDER BY r.Row)
UNION(
SELECT r.Row
, 'Households' AS Col
, CASE r.Row
WHEN '< 70%' THEN IFNULL(d.c70,0)
WHEN '< 80%' THEN IFNULL(d.c80,0)
WHEN '< 90%' THEN IFNULL(d.c90,0)
WHEN '< 100%' THEN IFNULL(d.c100,0)
END AS Data
, 7 AS Seq
FROM ( SELECT '< 70%' AS Row
UNION ALL SELECT '< 80%'
UNION ALL SELECT '< 90%'
UNION ALL SELECT '< 100%'
) r
CROSS
JOIN ( SELECT SUM(vp.mail_propensity >= 0.70 AND p.perm_absentee = 'Y') AS c70
, SUM(vp.mail_propensity >= 0.80 AND p.perm_absentee = 'Y') AS c80
, SUM(vp.mail_propensity >= 0.90 AND p.perm_absentee = 'Y') AS c90
, SUM(vp.mail_propensity >= 1 AND p.perm_absentee = 'Y') AS c100
, COUNT(p.HHKey) AS count_HHKey
FROM VoterProfile p
JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
WHERE d.district_id = target
AND vp.election_type = 'T'
GROUP BY HHKey
) d
ORDER BY r.Row
)
;
END
then use a second procedure that call the first procedure
CREATE PROCEDURE `district_propensity`(IN target varchar(30))
COMMENT 'display demographics for district'
BEGIN
CALL district_Propensity_counts(target);
SELECT district_id, district_name FROM Districts WHERE district_id=target;
SELECT Row,
GROUP_CONCAT(IF (COL='Voters',Data,NULL))+0 AS 'Voters',
GROUP_CONCAT(IF (COL='Households',Data, NULL))+0 AS 'Households'
FROM Temp
GROUP BY ROW
ORDER BY SEQ;
END
but the output i get when i do:
CALL district_propensity('3')
is something like
| Row | Voters | Households |
| Total | 15235 | 7250 |
| Poll | NULL | NULL |
| <90% | 4565 | 1 |
| AV | NULL | NULL |
| <100% | 668 | 0 |
| <70% | 1894 | 0 |
| <80% | 1434 | 3 |
| PAV | NULL | NULL |
thats it... im missing all the others.. but the numbers in the voters column is correct.. its counting that right but missing a whole lot..
any help on this is greatly appreciated
