SELECT COUNT and Data

Question

I have the following query which returns data with a limit of 5.

SELECT a.* FROM (
    SELECT category, description, price, date_added, datetime_created
    FROM vc_expense e1
    WHERE trip_id=:trip_id AND description LIKE :search
    UNION ALL
    SELECT category, description, NULL, NULL, NULL
    FROM vc_expense_default e2
    WHERE description LIKE :search
) AS a
GROUP BY description, price
ORDER BY CASE
    WHEN price IS NOT NULL THEN 1
    WHEN description LIKE :search_start THEN 2
    WHEN description LIKE :search THEN 3
    ELSE 4
END, datetime_created DESC, date_added DESC
LIMIT 5

Everything works as intended, but I want to also include how many items was also returned before the LIMIT. Something like this would be good:

{
    count: 32,
    data: [items, limited to 5]
}

I have tried the following with no luck:

SELECT COUNT(a.*) AS count, a.* AS data FROM (

Your query is invalid, because you are doing `GROUP BY` with `SELECT *`. At this point, you should add sample data to your question which explains what you are trying to do here. — Tim Biegeleisen, Sep 27 '18 at 08:06
Perhaps you are looking for found_rows() function https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows — P.Salmon, Sep 27 '18 at 08:10
@TimBiegeleisen The query is working. I think I put it pretty clear, but what I am trying to accomplish is to get my 5 items, but also include the count of how many items was found in the query. — Tompina, Sep 27 '18 at 08:43
Please include sample input and output data so that we know exactly what you have in mind. By the way, if you continue writing SQL code like this, you will eventually find big problems later on. — Tim Biegeleisen, Sep 27 '18 at 08:44
@TimBiegeleisen On it, give me a few minutes! How will this cause big problems later on? Performance issues? — Tompina, Sep 27 '18 at 08:46
@Tompina Please post general comments about your question here, not under others' answers. Why are you using `GROUP BY` in the first place? You aren't computing any aggregates. What is your query supposed to be doing? — Tim Biegeleisen, Sep 27 '18 at 09:05
@TimBiegeleisen Got it! So the reason I use GROUP BY is because some items have the same description and price, which I want to group (Remove duplicates basically). — Tompina, Sep 27 '18 at 09:07
OK, if two records have the same `description` and `price`, then what is your logic for deciding which of the two records you want to show? There are other columns, such as `price`, which you are selecting. _Which_ price do you choose? — Tim Biegeleisen, Sep 27 '18 at 09:08
@TimBiegeleisen The ORDER BY will make sure items with a price goes on top of the query, which will most likely be cut out by the LIMIT. If there are no price, it will return NULL as expected (The program is fine with this) — Tompina, Sep 27 '18 at 09:11
SQL doesn't work like this. Your `LIMIT` statement applies to the entire result set, once, but you need a way to restrict each _group_ to a certain record. Sample input and output would help you to get a correct answer. — Tim Biegeleisen, Sep 27 '18 at 09:12

Madhur Bhaiya · Accepted Answer · 2018-09-27T09:24:51.907

2

You need to use FOUND_ROWS() with SQL_CALC_FOUND_ROWS called during the Select statement. Without SQL_CALC_FOUND_ROWS being called, FOUND_ROWS() will simply return 5 (your Limit).

Use the following:

SELECT SQL_CALC_FOUND_ROWS a.* FROM (
    SELECT category, description, price, date_added, datetime_created
    FROM vc_expense e1
    WHERE trip_id=:trip_id AND description LIKE :search
    UNION ALL
    SELECT category, description, NULL, NULL, NULL
    FROM vc_expense_default e2
    WHERE description LIKE :search
) AS a
GROUP BY description, price
ORDER BY CASE
    WHEN price IS NOT NULL THEN 1
    WHEN description LIKE :search_start THEN 2
    WHEN description LIKE :search THEN 3
    ELSE 4
END, datetime_created DESC, date_added DESC
LIMIT 5

After firing this query, you need to fire another query, to get the total number of rows.

SELECT FOUND_ROWS() AS overall_count_without_limit;

From Documentation:

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include an SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

Performance:

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

Additional Notes:

You should avoid using SELECT *. Read: Why is SELECT * considered harmful?
Also, whenever you are using GROUP BY, your SELECT list should contain only those columns which are either functionally dependent on columns in the GROUP BY clause, and/or Aggregated columns/expressions. Read: Error related to only_full_group_by when executing a query in MySql
In newer versions of MySQL (version >= 5.7.5), this query will throw error

edited Sep 27 '18 at 09:24

answered Sep 27 '18 at 08:47

Madhur Bhaiya

28,155
10
49
57

1

@Tompina slightly slower than your actual query. Because it will do the complete calculation to count the number of overall rows, and will not stop as soon as the limit is reached. But it is **still much better than** firing another query separately to count again. – Madhur Bhaiya Sep 27 '18 at 08:52
1

@Tompina this functionality is very useful for pagination related requirements. – Madhur Bhaiya Sep 27 '18 at 08:52
1

It should be noted that this query probably should _not_ be used in production, because it is doing `GROUP BY` with `SELECT *`. Yes, it may _run_, but there is no guarantee that it would always even return the same results. – Tim Biegeleisen Sep 27 '18 at 08:59
@TimBiegeleisen agreed. there are other issues with the original query. – Madhur Bhaiya Sep 27 '18 at 09:01
@TimBiegeleisen So if I replace SELECT * with the variables from the main query, it would make it better? I have tried to produce errors but I get nothing! – Tompina Sep 27 '18 at 09:03
@Tompina I have updated my answer. Check additional notes part. In a nutshell, you should avoid using SELECT * and if you are using group by, your select should contain only those columns which are functionally dependent on the columns in group by clause, or the ones using aggregated functions like Sum, Count Reason you are not generating error is because you are on older MySQL version, where Mysql was lenient – Madhur Bhaiya Sep 27 '18 at 09:07
I am using 5.7.19 and no errors get thrown. When I do *SELECT SQL_CALC_FOUND_ROWS description, price* instead, it gives me just the description and price, nothing else! – Tompina Sep 27 '18 at 09:22
@Tompina as I remember `only_full_group_by` mode has been made default from mysql version 5.7.5 onwards. Maybe upgrade to latest 5.7 version and check – Madhur Bhaiya Sep 27 '18 at 09:24

SELECT COUNT and Data

1 Answers1

Linked