Using DISTINCT keyword with count in SQL Query

Question

I have a page that is displaying a list of all company names (45 of them to be exact).

The database table has 43,815 results currently, so I only want to display them without duplicates (which is why I'm using the DISTINCT when I select) but I would also like to count how many results per company has and echo them out.

I'm tried using the count() as but it removes all the company results and just places the total (43,815).

My question is how would I display the companies using DISTINCT (because I don't want to have duplicates on the page) and then also echo out the total results of each company?

Any help is appreciated!

$servername = "localhost";
$username = "";
$password = "";
$dbname = "";

// Connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT DISTINCT company FROM ads ORDER BY company ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

       // Display the company name
       echo $row["company"];

       // I WOULD LIKE TO ECHO OUT HERE THE TOTAL OF EACH COMPANY HAS (COUNT) IN RESULTS!

    }
} else {
    echo "0 results";
  }
$conn->close();
?>

Answer 1

I think that you should probably try to use the GROUP BY clause.

Without knowing your table markup try playing around with this:

$query = "SELECT 

  count(company) as count 

FROM ads 

GROUP BY company 

ORDER BY company ASC"

Answer 2

Try:

SELECT
    company,
    COUNT(company)
FROM
    ads
GROUP BY
    company
ORDER BY
    company