CODEWITHSUNDEEP
mysqlsql
Muhammad Umar
Muhammad Umar

Reputation: 11782

SELECT Distinct table items from a table in mysql

I am trying to find out total count of clientIds that are distinct in my table

here are the queries

            $stmt = $conn->prepare("SELECT DISTINCT count(clientId) as totalrecords FROM quotes WHERE storeEmail = '". $store['email']. "'");
    $stmt->execute();
    $totalRecords = $stmt->fetch(PDO::FETCH_COLUMN);

            echo 'Total Users Found with Request Quote : ' . $totalRecords . "<br /><br />"; 
            echo 'Press Send button to send notifications to all users <br /><br />'; 

            $query = "SELECT DISTINCT clientId FROM quotes WHERE storeEmail = '". $store['email']. "'";
            $stmt = $conn->prepare($query);
    $stmt->execute();                                
    $clients = $stmt->fetchAll(PDO::FETCH_COLUMN);

The first query gives me sum of 147 whereas the second query gives me 60

What is wrong with first query.

Upvotes: 1

Views: 365

Answers (3)

VMai
VMai

Reputation: 10336

Two things:

The DISTINCT got to go into the COUNT. If you use prepared statements, you should use parameterized queries like:

$stmt = $conn->prepare("
    SELECT count(DISTINCT clientId) as totalrecords FROM quotes WHERE storeEmail = ?");
$stmt->execute(array($store['email']));

$totalRecords = $stmt->fetch(PDO::FETCH_COLUMN);

echo 'Total Users Found with Request Quote : ' . $totalRecords . "<br /><br />"; 
echo 'Press Send button to send notifications to all users <br /><br />'; 

$query = "SELECT DISTINCT clientId FROM quotes WHERE storeEmail = ?";
        $stmt = $conn->prepare($query);
$stmt->execute(array($store['email']));                                
$clients = $stmt->fetchAll(PDO::FETCH_COLUMN);

Upvotes: 0

Gaurav
Gaurav

Reputation: 28755

SELECT DISTINCT count(clientId) will return you count of clientId , whether you use DISTINCT OR not.

But the second query will return the distinct clietIds.

And you should to use

SELECT count(DISTINCT clientId)

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

COUNT DISTINCT is what you're looking for:

$query = "SELECT COUNT(DISTINCT clientId) FROM quotes WHERE storeEmail = '". $store['email']. "'";

Upvotes: 1

Related Questions