CODEWITHSUNDEEP
mysqlgroup-by
user892134
user892134

Reputation: 3224

Mysql Group by clause help

$construct = "SELECT * FROM mytable GROUP BY nid HAVING nid>1";

    mytable:
+----+----------+
| id |    nid   |
+----+----------+
|  1 |     null |
|  2 |        1 |
|  3 |        1 |
|  4 |        1 |
|  5 |        2 |
|  6 |        2 |
|  7 |        3 |
|  8 |        3 |
|  9 |        4 |
| 10 |        4 |
-----------------

How do i GROUP BY nid except nid=1? This is a brief example but with my code i am not getting the desired results. Is the query correct for what i am trying to accomplish?

Upvotes: 1

Views: 80

Answers (3)

Andriy M
Andriy M

Reputation: 77677

…
GROUP BY CASE nid WHEN 1 THEN -id ELSE nid END
…

Upvotes: 0

HJW
HJW

Reputation: 23443

SELECT count(*), nid FROM mytable where nid <> 1 GROUP BY nid;

or 

SELECT count(*), nid FROM mytable where nid != 1 GROUP BY nid;

Not sure if you are using Oracle or MySQL.

Upvotes: 1

Kerrek SB
Kerrek SB

Reputation: 477040

How about this:

SELECT * FROM mytable WHERE nid != 1 ORDER BY nid

GROUP BY causes an aggregate query which you can only sensibly use with an aggregation function. For example, SELECT COUNT(*), nid GROUP BY nid would give you the counts of rows with a given nid.

Update: Not sure I'm understanding you, but how about this then:

(SELECT * FROM mytable WHERE nid = 1 UNION SELECT * FROM mytable WHERE nid != 1 GROUP BY nid) ORDER BY nid

I'm not sure it makes sense to mix aggregate and non-aggregate queries, though -- on the aggregate side you'll just end up with an indeterminate representative row of that group.

Upvotes: 1

Related Questions