List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 9 1999 8:23pm
Subject:Re: count
View as plain text  
On Fri, 1999-10-08 22:57:40 -0400, Frank Oellien wrote:
> I have the following problem:
> 
> 1 Table:
> 
> NSCNr | CellID | Konz.
>  123  |   1    |  2
>  123  |   2    |  2
>  123  |   3    |  2
>  123  |   1    |  2.5
>  123  |   2    |  2.5
>  124  |   1    |  2
>  124  |   3    |  3
>  125  |   1    |  1.6
>  125  |   2    |  1.5
>  126  |   2    |  1.5
>  126  |   4    |  1.5
> 
> I want the following result (only count the different NSCNr for one
> CellID)
> 
> CellID | count(NSCNr)
>   1    |     3
>   2    |     3
>   3    |     2
>   4    |     1
> 
> But when I use this command
> select CellID, count(NSCNr) from tgi group by CellID;
> I become this result:
> 
> CellID | count(NSCNr)
>   1    |     4
>   2    |     4
>   3    |     2
>   4    |     1
> 
> Of course it is correct, but how must the command be to return the
> first result?

Exactly for this purpose, standard SQL das a variant of COUNT:
  COUNT(DISTINCT ...)
And using this your query would be:
  SELECT CellID, COUNT(DISTINCT NSCNr) FROM tgi GROUP BY CellID;

Unfortunately MySQL supports COUNT(DISTINCT ...) only starting with
version 3.23.x!


If you're using an older version of MySQL, there are different
work-arounds to finally get the desired result:

- Do the counting yourself on application side:
    SELECT DISTINCT NSCNr, CellID FROM tgi ORDER by CellID;
  While retrieving the result set, you have to count yourself
  how many rows per CellID there are.

- Using a temporary table:
    CREATE TABLE tmp ( NSCNr INT, CellID INT );
    INSERT INTO tmp SELECT DISTINCT NSCNr, CellID FROM tgi;
    SELECT CellID, COUNT(NSCNr) FROM tmp GROUP BY CellID;
    DROP TABLE tmp;
  The crucial point again is SELECT DISTINCT which guarantees
  that no duplicates are stored in the temporary table.

  Main disadvantage of this method is the additional required storage
  space.  And if several instances of this query are running at the
  same time, you also have to prevent concurrent access to the
  temporary table -- either by locking, or by giving the temporary
  tables different, unique names.  The latter is, what CREATE
  TEMPORARY TABLE does in MySQL V3.23.x ...

- Using a self join:

    SELECT t1.CellID,  count(t1.NSCNr) AS NSCNr
      FROM   tgi AS t1 LEFT JOIN
             tgi AS t2 ON t1.NSCNr=t2.NSCNr
                      AND t1.CellID=t2.CellID
                      AND t1.id<t2.id
      WHERE  t2.NSCNr IS NULL
      GROUP BY CellID;

  assuming that the column id has an unique index, or is the primary
  key.

  Because then for each (CellID,NSCNr) pair the corresponding group of
  rows has exactly one biggest row, which therefore can't find a
  matching partner on the right side (there is no bigger t2.id then,
  if t1.id is the biggest one).  So just in this case the right side
  has NULL values, which can be tested in the WHERE clause.
  The result is, that only distinct pairs of (CellID,NSCNr) values
  are left ...


Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
countFrank Oellien9 Oct
  • Re: countMartin Ramsch9 Oct
  • Re: countThimble Smith9 Oct