CODEWITHSUNDEEP
phpmysqlcountsql-order-by
gab
gab

Reputation: 77

mySQL how to get last date visible in a COUNT grouped by another column and ordered by date

I have a table with columns these columns:

date
brand
order_number

I need to show a report grouped by brand, ordered by date descending, counted by order_number, showing the most recent date of the last order for each brand.

My code is like this:

<?php
$sql = "SELECT *, COUNT(*) FROM table GROUP BY brand order by date DESC;";
$resultg = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)) {
    $date = $row["date"];
    $brand = $row["brand"];
    echo "<table>";
        echo "<tr>";  
            echo "<td class='timecell'>";
                echo date('d-m H:i.s', strtotime($date));
            echo "</td>";
            echo "<td class='brandcell'>";
                echo $brand;
            echo "</td>";
            echo "<td class='countcell'>";
                echo $row['COUNT(*)'];
            echo "</td>";
        echo "</tr>";
    echo "</table>";
}
?>

The output I get is ordered by date descending and it shows how many orders for each brand, but the date showed for each group is the date of the first order for that brand, and also the order in the list is made using the date of the first order for that brand, while I need to show the date of the last order for that brand and have the list ordered descending using the date of the last order for that brand. How must be written the query to get that result?

Upvotes: 0

Views: 33

Answers (3)

gab
gab

Reputation: 77

Found the solution.

The goal was to group the orders by brand, to show the date of the last order for that brand and to have the list ordered descending by the date of the last order for each brand.

The sql syntax to get the goal is:

SELECT *, MAX(date), COUNT(*) FROM table GROUP BY brand order by MAX(date) DESC;

This produce the correct result and can be formatted like follows:

<?php

$sql = "SELECT *, COUNT(*) FROM table GROUP BY brand order by date DESC;";
$resultg = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)) {
    $brand = $row["brand"];
    echo "<table>";
        echo "<tr>";  
            echo "<td class='timecell'>";
                $row['MAX(date)'];
            echo "</td>";
            echo "<td class='brandcell'>";
                echo $brand;
            echo "</td>";
            echo "<td class='countcell'>";
                echo $row['COUNT(*)'];
            echo "</td>";
        echo "</tr>";
    echo "</table>";
}

?>

Upvotes: 0

nbk
nbk

Reputation: 49385

This query would show all orders sorted by date and how many orders of the brand you got.

SELECT t1.*, count_b 
FROM table1 t1 
    INNER JOIN (SELECT brand, COUNT(*) count_b FROM table1 GROUP BY brand) b ON t1.brand = b.brand 
 order by date DESC

Upvotes: 0

Let&#39;s try
Let&#39;s try

Reputation: 1058

Maybe using a MAX(date) together with the COUNT(*) if I understood correctly?

Upvotes: 0

Related Questions