get mysql COUNT in PHP

Question

I'm running the a query to return all results where a serial number has been entered more than once as follows:

SELECT * FROM spin
            WHERE `serial` in (
            SELECT `serial` from spin group by `serial`
            HAVING count(*) > 1
            ) ORDER BY date ASC

    $query = $this->db->prepare($sql);
    $query->execute();
    return $query->fetchAll();

It works as expected but I want to filter this out to only show one of the values with its count of occurrences. I'm guessing it will need some sort of distinct adding to the query to get a unique value back but not sure how to implement this in the best way?

For anyone intrest this is how I'm returning the data (code shortened):

                        <thead>
                        <tr>
                            <th>Serial</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($spins as $spin) { ?>
                            <tr>
                                <td><?php if (isset($spin->serial)) echo $spin->serial; ?></td>
                                <td><?php if (isset($spin->test_started)) echo $spin->date; ?></td>
                            </tr>
                        <?php } ?>
                    </tbody>

Do you want to apply limit 1?

Anil
– Anil

2014-12-11 14:28:07 +00:00
Commented Dec 11, 2014 at 14:28 — Anil
– Anil, Commented Dec 11, 2014 at 14:28

Barmar · Accepted Answer · 2014-12-11 14:26:14Z

2

This query will do it:

SELECT *, COUNT(*) AS count
FROM spin
GROUP BY serial
HAVING count > 1
LIMIT 1

answered Dec 11, 2014 at 14:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

server_kitten · Accepted Answer · 2014-12-11 14:42:32Z

0

To avoid nested select statements, use GROUP BY.

SELECT serial, COUNT(*) AS count FROM spin 
     GROUP BY serial HAVING count > 1 
     ORDER BY date ASC LIMIT 1;

answered Dec 11, 2014 at 14:42

server_kitten

3863 silver badges13 bronze badges

Comments

ymas · Accepted Answer · 2014-12-11 14:55:19Z

Here are my assumptions, you want to return a list of serials that are repeated in your spin table, however, you only to show the repeated serial once. With your current query, you are fetching all the records in your spin table with duplicate serials.

The query below will return all the serials that have a count > 1, however, it will only show each serial once.

SELECT `serial`, COUNT(`serial`) AS serial_count, `date`
FROM `spin`
GROUP BY `serial`
HAVING serial_count > 1
ORDER BY `date` ASC;

If you want to see all the dates, try this:

SELECT `serial`, COUNT(`serial`) AS serial_count, GROUP_CONCAT(`date`) AS dates
FROM `spin`
GROUP BY `serial`
HAVING serial_count > 1
ORDER BY `date` ASC;

You could add a LIMIT at the end if you want to restrict the number of records your query returns.

Collectives™ on Stack Overflow

get mysql COUNT in PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related