Need to add total to Union query

I have a bunch of kids ages I’m counting up from a family event registration db in php and myql. The kids are in groups (tribes), each tribe group has a row with the ages groups in columns. The union queries work well. However, I need to add a total for the rows and a total for each column. I’m quite stuck and am over my head. The results so far look like this:

[table=“width: 750, class: grid, align: center”]
[tr]
[td]Tribe[/td]
[td]Age 3[/td]
[td]Age 4[/td]
[td]Age 5[/td]
[td]Age 6[/td]
[td]Age 7[/td]
[td]Age 8[/td]
[td]Age 9[/td]
[td]Age 10[/td]
[td]Age 11[/td]
[td]Age 12[/td]
[td]Age 13[/td]
[td]Age 14[/td]
[td]Age 15[/td]
[td]Age 16[/td]
[td]Age 17[/td]
[/tr]
[tr]
[td]Apache[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]2[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Arapahoe[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]3[/td]
[td]2[/td]
[td]5[/td]
[td]1[/td]
[td]2[/td]
[td]4[/td]
[td]3[/td]
[td]3[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Aztec[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]3[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]2[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Blackfoot[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]5[/td]
[td]2[/td]
[td]0[/td]
[td]2[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Cherokee[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]3[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]3[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Chippewa[/td]
[td]0[/td]
[td]0[/td]
[td]2[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]4[/td]
[td]1[/td]
[td]0[/td]
[td]6[/td]
[td]3[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[/tr][tr]
[td]Creek[/td]
[td]0[/td]
[td]0[/td]
[td]2[/td]
[td]2[/td]
[td]1[/td]
[td]2[/td]
[td]2[/td]
[td]0[/td]
[td]2[/td]
[td]2[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Fox[/td]
[td]0[/td]
[td]0[/td]
[td]2[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]2[/td]
[td]5[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]2[/td]
[td]1[/td]
[td]0[/td]
[/tr][tr]
[td]Iroquois[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]3[/td]
[td]2[/td]
[td]1[/td]
[td]3[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Mohawk[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]4[/td]
[td]4[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]2[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Pawnee[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]2[/td]
[td]2[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr][tr]
[td]Yellowknives[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]2[/td]
[td]1[/td]
[td]7[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[/tr]
[/table]

$WhoToCount = "Daughter";
$query2 = "SELECT custom, item_name1, 'Total' AS TRIBE ";

for ($i = 3; $i <= 17; $i++) {
$query2 .= ", (SELECT count(item_name1) AS Quantity
        FROM
            (SELECT item_name1, item_number1, item_number2, item_name3 AS Item, 1 AS Count, item_number3 AS Ages, custom
            	FROM tbl_pp_transactions WHERE item_name3 = '".$WhoToCount."' AND custom = ".$colname_rsEventID."
			UNION ALL SELECT item_name1, item_number1, item_number2, item_name4 AS Item, 1 AS Count, item_number4 AS Ages, custom
            	FROM tbl_pp_transactions WHERE item_name4 = '".$WhoToCount."' AND custom = ".$colname_rsEventID."
			UNION ALL SELECT item_name1, item_number1, item_number2, item_name5 AS Item, 1 AS Count, item_number5 AS Ages, custom
            	FROM tbl_pp_transactions WHERE item_name5 = '".$WhoToCount."' AND custom = ".$colname_rsEventID."
			UNION ALL SELECT item_name1, item_number1, item_number2, item_name6 AS Item, 1 AS Count, item_number6 AS Ages, custom
            	FROM tbl_pp_transactions WHERE item_name6 = '".$WhoToCount."' AND custom = ".$colname_rsEventID.") AS s
        WHERE item_name3 = '".$WhoToCount."' AND custom = ".$colname_rsEventID." AND item_number1 = ca.item_number1 AND Ages = ".$i.") AS 'Age ".$i."' ";
}

$query2 .= "FROM tbl_pp_transactions ca
WHERE item_name3 = '".$WhoToCount."' AND custom = ".$colname_rsEventID."
GROUP BY TRIBE;
";

Then the prepared statement

<?php
if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($custom, $item_name1, $TRIBE, $Quantity, $Item, $Item1, $Item3, $Item5, $Item7, $Item9, $Item11, $Item13, $Item15, $Item17, $Item19, $Item21, $Item23, $Item25);
?>
					<table border="1" cellpadding="1" style="background-color: #feeebd; font-weight: bold;">
						<tr style="font-weight: bold; font-size: 13px; background-color: #fece2f">
							<th width="75" scope="col">Tribe</th>
							<th scope="col">Age 3</th>
							<th scope="col">Age 4</th>
							<th scope="col">Age 5</th>
							<th scope="col">Age 6</th>
							<th scope="col">Age 7</th>
							<th scope="col">Age 8</th>
							<th scope="col">Age 9</th>
							<th scope="col">Age 10</th>
							<th scope="col">Age 11</th>
							<th scope="col">Age 12</th>
							<th scope="col">Age 13</th>
							<th scope="col">Age 14</th>
							<th scope="col">Age 15</th>
							<th scope="col">Age 16</th>
							<th scope="col">Age 17</th>
						</tr>
						<?php
							while ($stmt->fetch()) {
								echo "<tr class='body_black'>";
								printf("
								<td><strong>%s</strong></td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								", $TRIBE, $Quantity, $Item, $Item1, $Item3, $Item5, $Item7, $Item9, $Item11, $Item13, $Item15, $Item17, $Item19, $Item21, $Item23, $Item25);
								echo "</tr>";

							}
							$stmt->close();
						}
						?>
					</table>

I’m having trouble finding information on how to add summing to a count. Does anyone have any ideas?

Thanks

Just noticed that I mislabeled the variable $query2, it’s supposed to be $query. Only a typo in this thread though.

I’ve simplified my coding to help get through this.

I have a bunch of kid’s ages I’m counting up from data in a family event registration db built in php and mysql. The kids are registering themselves and declaring the tribe group and age they belong to. The total count of kids each column is nit working. The results so far look like this:

    custom 	item_number1 	Age 7 	Age 8
    703198 	Apache 	          1 	NULL
    703198 	Arapahoe 	  2  	5
    703198 	Aztec 	          1 	NULL
    703198 	Blackfoot 	  1 	5
    703198 	Cherokee 	  1 	1
    703198 	Chippewa 	  1 	1
    703198 	Creek 	          1 	2
    703198 	Fox 	          1 	1
    703198 	Iroquois 	  1 	NULL
    703198 	Mohawk 	       NULL	4
    703198 	Pawnee 	          1 	2
    703198 	Yellowknives 	  2 	1
    703198 	All Tribes 	   2 	1

The correct Total for Age 7 is 13, rather than just repeating the previous row.

Here’s the code:

    SELECT custom, IFNULL(item_number1,'All Tribes') AS item_number1,

       (SELECT sum(Count)
        FROM
    	   (SELECT custom, item_number1, item_name3 , 1 AS Count, item_number3 AS Ages
    	    FROM tbl_pp_transactions WHERE item_name3 = 'Daughter'

        	UNION ALL SELECT custom, item_number1, item_name4, 1 AS Count, item_number4 AS Ages
        	FROM tbl_pp_transactions WHERE item_name4 = 'Daughter'
            ) AS s
        WHERE custom = 703198 AND item_number1  = ca.item_number1 AND Ages = 7) AS 'Age 7'

       ,(SELECT sum(Count)
        FROM
        	(SELECT custom, item_number1, item_name3 , 1 AS Count, item_number3 AS Ages
    	    FROM tbl_pp_transactions WHERE item_name3 = 'Daughter'

    	    UNION ALL SELECT custom, item_number1, item_name4, 1 AS Count, item_number4 AS Ages
    	    FROM tbl_pp_transactions WHERE item_name4 = 'Daughter'
            ) AS s
         WHERE custom = 703198 AND item_number1  = ca.item_number1 AND Ages = 8) AS 'Age 8'

    FROM tbl_pp_transactions AS ca
    WHERE item_name3 = 'Daughter' AND custom = 703198
    GROUP BY item_number1 WITH ROLLUP

Any suggestions or direction would be great?

Thanks

i think your problem might go away if you simply change the alias name on your IFNULL

please try this simplification and see if it works –

SELECT 703198 AS custom
     , COALESCE(item_number1,'All Tribes') AS item_number
     , COUNT(CASE WHEN Ages = 7 THEN 7 ELSE NULL END) AS 'Age 7'
     , COUNT(CASE WHEN Ages = 8 THEN 8 ELSE NULL END) AS 'Age 8'
  FROM ( SELECT item_number1
              , item_number3 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name3 = 'Daughter'
            AND item_number3 IN ( 7 , 8 )
         UNION ALL
         SELECT item_number1
              , item_number4 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name4 = 'Daughter'
            AND item_number4 IN ( 7 , 8 )
       ) AS dt
GROUP
    BY item_number1 WITH ROLLUP

Thank you! It worked perfectly. So much more straight forward. I can ultimately loop through a do while to fill it out for all the ages so that the query statement looks like this:

<?php
$query = "SELECT 703198 AS custom, item_name1
     , COALESCE(item_number1,'All Tribes') AS item_number1
     , COUNT(CASE WHEN Ages = 4 THEN 4 ELSE NULL END) AS 'Age 4'
     , COUNT(CASE WHEN Ages = 5 THEN 5 ELSE NULL END) AS 'Age 5'
     , COUNT(CASE WHEN Ages = 6 THEN 6 ELSE NULL END) AS 'Age 6'
     , COUNT(CASE WHEN Ages = 7 THEN 7 ELSE NULL END) AS 'Age 7'
     , COUNT(CASE WHEN Ages = 8 THEN 8 ELSE NULL END) AS 'Age 8'
     , COUNT(CASE WHEN Ages = 9 THEN 9 ELSE NULL END) AS 'Age 9'
     , COUNT(CASE WHEN Ages = 10 THEN 10 ELSE NULL END) AS 'Age 10'
     , COUNT(CASE WHEN Ages = 11 THEN 11 ELSE NULL END) AS 'Age 11'
     , COUNT(CASE WHEN Ages = 12 THEN 12 ELSE NULL END) AS 'Age 12'
     , COUNT(CASE WHEN Ages = 13 THEN 13 ELSE NULL END) AS 'Age 13'
     , COUNT(CASE WHEN Ages = 14 THEN 14 ELSE NULL END) AS 'Age 14'
     , COUNT(CASE WHEN Ages = 15 THEN 15 ELSE NULL END) AS 'Age 15'
     , COUNT(CASE WHEN Ages = 16 THEN 16 ELSE NULL END) AS 'Age 16'
     , COUNT(CASE WHEN Ages = 17 THEN 17 ELSE NULL END) AS 'Age 17'
   FROM ( SELECT item_number1
              , item_name1
              , item_name3 AS Item
              , item_number3 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name3 = 'Daughter'
            AND item_number3 IN (4,5,6,7,8,9,10,11,12,13,14,15,16,17)
         UNION ALL
         SELECT item_number1
              , item_name1
              , item_name4 AS Item
              , item_number4 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name4 = 'Daughter'
            AND item_number4 IN (4,5,6,7,8,9,10,11,12,13,14,15,16,17)
         UNION ALL
         SELECT item_number1
              , item_name1
              , item_name5 AS Item
              , item_number5 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name5 = 'Daughter'
            AND item_number5 IN (4,5,6,7,8,9,10,11,12,13,14,15,16,17)
         UNION ALL
         SELECT item_number1
              , item_name1
              , item_name6 AS Item
              , item_number6 AS Ages
           FROM tbl_pp_transactions
          WHERE custom = 703198
            AND item_name6 = 'Daughter'
            AND item_number6 IN (4,5,6,7,8,9,10,11,12,13,14,15,16,17)
       ) AS dt
GROUP
    BY item_number1 WITH ROLLUP";
?>

It’s so different that I’m having trouble turning the output into the prepared statement. Here’s the original statement:

<?php
if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($custom, $item_name1, $item_number1, $Ages, $Item, $Item1, $Item3, $Item5, $Item7, $Item9, $Item11, $Item13, $Item15, $Item17, $Item19, $Item21, $Item23, $Item25);
?>
					<table border="1" cellpadding="1">
						<tr>
							<th>Tribe</th>
							<th>Age 4</th>
							<th>Age 5</th>
							<th>Age 6</th>
							<th>Age 7</th>
							<th>Age 8</th>
							<th>Age 9</th>
							<th>Age 10</th>
							<th>Age 11</th>
							<th>Age 12</th>
							<th>Age 13</th>
							<th>Age 14</th>
							<th>Age 15</th>
							<th>Age 16</th>
							<th>Age 17</th>
						</tr>
						<?php
							while ($stmt->fetch()) {
								echo "<tr class='body_black'>";
								printf("
								<td><strong>%s</strong></td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								<td align='center'>%s</td>
								", $item_number1, $Ages, $Item, $Item1, $Item3, $Item5, $Item7, $Item9, $Item11, $Item13, $Item15, $Item17, $Item19, $Item21, $Item23, $Item25);
								echo "</tr>";

							}
							$stmt->close();
						}
						?>
					</table>

The final HTML table has only the tribe name (item_number1) and the Ages columns. I suspect that I’m not naming $Ages correctly. I don’t really understand why the prepared statement needs to use odd numbered ‘Item’ variables ($Item, $Item1, $Item3…)

i can’t help you with the php, but don’t forget for your ROLLUP to work, i think you need to have a different COALESCE alias name than item_number1

You were right. Needs to be

$Ages, $Ages1, $Ages3, $Ages5, $Ages7, $Ages9, $Ages10, $Ages13, $Ages15, $Ages17, $Ages19, $Ages21, $Ages23, $Ages25

Thanks for all your help!