Hi Guys I have a question. I am still learning and am trying to get some date out. Beneath is the table. It has hundreds of lines, but for example:

The table name is 'forms'. Now what I need to do is to count XX1 XX2 XX3 (for a year rapport). Then I have a 'date from and to' selection box on my page. So the question would be:

What instanties have been used between a certain date in total but so that you can see a a total per Instantie (each number is a different instantie).

So for example...Between the 1st of January and the 1st of June a list of all XX numbers ( there are 36 ) with there total behind it

What I have is the following. Is works great and shows all XX's in a nice table but for the entire table, not per date. As soon as i want to add the 'between $date_from AND $date_to' it fails.

 <?php
            
  $sql_rg_total="SELECT forms.Datum, x.f1,Count(x.f1) 
             FROM
            (SELECT XX1 As F1 FROM forms
             UNION ALL
             SELECT XX2 As F1 FROM forms
             UNION ALL
             SELECT XX3 As F1 FROM forms) x
             WHERE x.f1 = '$subcat_id' 
             GROUP BY x.f1";
            
             $resultvv=mysqli_query($conn, $sql_rg_total);
                    if (mysqli_num_rows($resultvv) > 0) {
                        while ($rowvv = mysqli_fetch_assoc($resultvv)) {
                            $subnr = $rowvv['Count(x.f1)'];
                            
                            echo $subnr;
            
                            }
                    }
                        
?>

By the way $subcat_id is from another table which connects the number to a name.

I have tried to write it as clear as I could. I know it's a bit thought haha. Thanks anyway for any input. Really stuck.

CodePudding user response：

This query should do it:

SELECT SUM(x.c) AS c
FROM (
    SELECT ((XX1 = '$subcat_id')   (XX2 = '$subcat_id')   (XX3 = '$subcat_id')) AS c
    FROM forms
    WHERE Datum BETWEEN '$date_from' AND '$date_to'
) x

The value of a boolean condition is 1 when it's true, 0 when it's false. So XX1 = '$subcat_id' XX2 = '$subcat_id' XX3 = '$subcat_id' adds up the number of columns that match in a row, then SUM(c) totals them in the entire table.

You don't need GROUP BY, since it's the same column that you're filtering in the WHERE condition (and now in the SELECT expression). And this moves the date condition into the subquery.