CODEWITHSUNDEEP
mysqlgroup-by
JohnnyeM
JohnnyeM

Reputation: 31

conditional group by

I need to apply group by only for a particular data.

for ex: 

id name
1  xx
2  xx
3  xx
4  yy
5  yy
6  zz

and after applying group by to xx it should be like

1 xx
4 yy
5 yy
6 zz

Upvotes: 0

Views: 833

Answers (3)

MikeyKennethR
MikeyKennethR

Reputation: 608

select distinct id, name from table order by id asc;

Upvotes: -1

anothershrubery
anothershrubery

Reputation: 20993

I would use a UNION

SELECT MIN(id), name
FROM MyTable
WHERE name = 'xx'
GROUP BY name

UNION

SELECT id, name
FROM MyTable
WHERE name <> 'xx'

But without much more info to go on this is most likely wrong.

Tip - Don't assume anybody knows what you are talking about, sometimes they will. But always try to spell things out with as much details as you can. You will get more insightful answers that way and more people will be inclined to help.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753515

Since you seem to want the minimum value of ID for 'xx', the original values of ID for 'yy' and the maximum value of ID for 'zz' (edit: though I now see that there is but one entry for 'zz', so it could be MIN instead of MAX, or it might be unaggregated), it is not clear that we can really generalize for you.

SELECT MIN(ID) AS ID, Name
  FROM AnonymousTable
 WHERE Name = 'xx'
 GROUP BY Name
UNION
SELECT ID      AS ID, Name
  FROM AnonymousTable
 WHERE Name = 'yy'
UNION
SELECT MAX(ID) AS ID, Name
  FROM AnonymousTable
 WHERE Name = 'zz'
 GROUP BY Name

This fits the data given in the question. You might also have a two-branch UNION with 'yy' and not 'yy' treated differently.

You might be able to use a calculated column in a better defined, more systematic scenario:

SELECT CASE Name WHEN 'yy' THEN ID ELSE 1 END AS Pseudo_ID, Name -- , COUNT(*) AS number
  FROM AnonymousTable
 GROUP BY Pseudo_ID, Name

You would normally use some aggregate function (such as COUNT) in the select-list to warrant a GROUP BY clause; otherwise, you can use a simple DISTINCT to eliminate duplicates.

Upvotes: 0

Related Questions