Geek asks and answers

SQL Query - Subquery returns more than one row

Table:

laterecords
-----------
studentid - varchar
latetime - datetime
reason - varchar

My request:

SELECT laterecords.studentid,
laterecords.latetime,
laterecords.reason,
( SELECT Count(laterecords.studentid) FROM laterecords 
      GROUP BY laterecords.studentid ) AS late_count 
FROM laterecords

I get the error "MySQL subquery of more than one row".

I know a workaround for this request to use the following request:

SELECT laterecords.studentid,
laterecords.latetime,
laterecords.reason 
FROM laterecords

Then, using php loop, although we get the results, and make a request below to get late_countand execute the echo code:

SELECT Count(laterecords.studentid) AS late_count FROM laterecords

But I think there might be a better solution?

+3
source share
1 answer

A simple fix is ​​to add a sentence WHEREto your subquery:

SELECT
    studentid,
    latetime,
    reason,
    (SELECT COUNT(*)
     FROM laterecords AS B
     WHERE A.studentid = B.student.id) AS late_count 
FROM laterecords AS A

The best option (in terms of performance) is to use a connection:

SELECT
    A.studentid,
    A.latetime,
    A.reason,
    B.total
FROM laterecords AS A
JOIN 
(
    SELECT studentid, COUNT(*) AS total
    FROM laterecords 
    GROUP BY studentid
) AS B
ON A.studentid = B.studentid
+3
source

More articles:


All Articles