Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL, finding total number of records as well as detail records
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

MySQL, finding total number of records as well as detail records

dnwkdnwk Member

I need help with a MySQL query.
I need to find out the total number of items in a database (which match my query) and getting some rows out.
If I wrote "SELECT count(ID), name ..... WHERE ....", the Query will only return the first row. The query is complex and I don't want to run the query twice just to get the head count. Counting items in PHP is unreliable as if I do "LIMIT 0,30" it will only tell you there are 30 items not the total number.

Is there a way to query the actual row as well as head count in one query?

Comments

  • EvoEvo Member

    You can combine the selects in one - example:

    http://sqlfiddle.com/#!2/ca27b/1

  • mikegmikeg Member

    As @Evo said, SELECT (SELECT count(ID) FROM mytable), name from mytable WHERE ....

    This is however very expensive, why not select it into a var and select that each time?

    select @var = count(ID) FROM mytable

    SELECT @var, name from mytable WHERE ....

  • dnwkdnwk Member

    @mikeg said:
    As Evo said, SELECT (SELECT count(ID) FROM mytable), name from mytable WHERE ....

    This is however very expensive, why not select it into a var and select that each time?

    select var = count(ID) FROM mytable

    SELECT var, name from mytable WHERE ....

    In that case, you essentially run the query twice

  • It seems to me that you are missing the 'group by', have you tried that?

  • mikegmikeg Member
    edited April 2014

    @dnwk said:
    In that case, you essentially run the query twice

    The query is only run once to get a total and then that total is used in all selects. It's a lot more efficient than running the select count on all rows.

  • mikegmikeg Member
    edited April 2014

    How many rows is your where query selecting? If its one, then use the nested select. If its more than 1 then my other solution is more efficient.

    Edit:
    Sorry just reread it and its only one row you are returning. Use the nested select.

  • dnwkdnwk Member
    edited April 2014

    @jrider said:
    It seems to me that you are missing the 'group by', have you tried that?

    Group by what? I have nothing to group by. I am selecting transaction logs that match certain condition and join it with other information like user profile

  • mikeg said: It's a lot more efficient than running the select count on all rows.

    Depends on the storage engine. It's instant for MyISAM but slow for InnoDB.

  • @dnwk said:
    Group by what? I have nothing to group by. I am selecting transaction logs that match certain condition and join it with other information like user profile

    To use a group function like count, you need to a group by clause, otherwise you just get a single row like you have found. In other databases it is a syntax error to omit the group by.

    I'm not sure exactly what you are trying to do, but it sounds like count() is not going to work for you.

    What do you mean by 'head count'? Are you wanting the count of the transactions?
    I work a lot with mysql and would be happy to assist, but it is hard without knowing what you need and the actual query.
    Direct message me if you want.

  • NickMNickM Member

    I get the feeling that you're asking the wrong question here. What I mean is that you're trying to get a particular outcome, and you're asking about a specific way to do what you think you want to do, when in reality, a different approach would probably work out better.

  • dnwkdnwk Member

    I already found a solution. Thanks

  • post the solution....

  • dnwkdnwk Member

    @seaeagle said:
    post the solution....

    I will. Those stuff were at the office. I will post it tomorrow

  • dnwkdnwk Member

    SELECT SQL_CALC_FOUND_ROWS * FROM *** And then run this Query " SELECT FOUND_ROWS()" Yes, it is two queries. But the second query is much much faster.

Sign In or Register to comment.