在一个查询中选择*，count（*）[重复]

Question

This question already has an answer here:

• SQL counting all rows instead of counting individual rows 1 answer

I've got a DB with a few columns and I'm trying to populate a html table with it. Everything's going fine but I've encountered the following problem:

Since I'm filling filtered Results into different Columns, I came up with a SQL Query that needs both Select * and count(*)?

$query = "SELECT *, COUNT(example_A) AS total_example_A FROM test WHERE example_A = 'certain_result' AND date(start_date) = '$current_date_proof' ORDER BY start_date ASC";

It does work, but I'm only getting the first result. I guess I cannot combine Select with Count?

</div>

Answer 1

You can do it with a correlated sub-query, Count is an aggregation function ( so it aggregates or combines all the data ):

$query = "
    SELECT
        t1.*,
        ( SELECT COUNT(t0.id) FROM test AS t0 WHERE t0.id = t1.id ) AS total_example_A
    FROM
        test AS t1
    WHERE
            t1.example_A = 'certain_result'
        AND
            date(t1.start_date) = '$current_date_proof'
    ORDER BY t1.start_date ASC
";

This assumes that your table test has a primary key named id. One other thing is I would count on the primary key if its not (example_A) COUNT(t0.id)

In my world a database either have a Auto Increment Int as the primary key or they have a compound primary key consisting of 2 or more foreign keys which are themselves Auto Increment Int fields. It's vital ( IMO ) to always have a surrogate key in you table. That is a key that has no direct relationship to the data itself. But, that is just me...

You could just count the return within your application, but barring that the correlated sub-query should give you the best/goodest performance. Certainly much better then a separate database call.