List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 11 2003 4:31am
Subject:Re: QUERY question (group by) - please help
View as plain text  
At 14:41 +0100 1/10/03, Damir Dezeljin wrote:
>Hi.
>
>I want to calculate how many rows my query which uses 'GROUP BY' returns.
>
>The query:
>----
>SELECT <something>
>     FROM test
>     WHERE (kid=1) OR (kid=2) OR (kid=4)
>     GROUP BY cid,aid;
>----
>
>Is it posible to get number of rows with such a query from MySQL v3.23.49?

You want both a list of rows, and a count of the number of rows -- with
a single query.  That doesn't make sense, because those two things are
incompatible.

>If it isn't posible ... is it posible in MySQL 4.x?

MySQL 4.x won't change the fundamental nature of the incompatibility.
The strategy you use below is reasonable.

>
>I think a lot about this problem and I realize only the following
>solution:
>CREATE TEMPORARY TABLE t (i INT);
>INSERT INTO t (i) SELECT aid FROM <the_query_above>
>SELECT COUNT(*) FROM t;
>DROP TABLE t;
>
>But this isn't so elegant.
>
>I want to do so on data generated by:
>----
>CREATE TABLE test (
>     kid INT,
>     aid INT,
>     cid INT
>);
>
>INSERT INTO test
>     (kid, aid, cid) VALUES
>     (  1,   0,   1),
>     (  2,   2,   2),
>     (  1,   3,   2),
>     (  2,   3,   2),
>     (  4,   4,   2),
>     (  4,   0,   3),
>     (  3,   3,   4),
>     (  4,   3,   4);
>----
>
>Regards,
>Dezo

Thread
QUERY question (group by) - please helpDamir Dezeljin10 Jan
  • Re: QUERY question (group by) - please helpPaul DuBois11 Jan
    • Re: QUERY question (group by) - please helpDamir Dezeljin11 Jan
      • Re: QUERY question (group by) - please helpJoseph Bueno11 Jan
        • Re: QUERY question (group by) - please helpDamir Dezeljin11 Jan
          • Re: QUERY question (group by) - please helpJoseph Bueno11 Jan
      • Re: QUERY question (group by) - please helpJocelyn Fournier11 Jan