Power By 专否社区

Most effective way to get value if select count(*) = 1 with grouping

Go To StackoverFlow.com

0

Lets say I have table with ID int, VALUE string:

ID    |  VALUE
1        abc 
2        abc 
3        def
4        abc
5        abc
6        abc

If I do select value, count(*) group by value I should get 

VALUE   | COUNT
abc       5
def       1

Now the tricky part, if there is count == 1 I need to get that ID from first table. Should I be using CTE? creating resultset where I will add ID string == null and run update b.ID = a.ID where count == 1 ?

Or is there another easier way?

EDIT:

I want to have result table like this:

ID      VALUE  count
null    abc    5
3       def    1
2012-04-04 21:56
by feronovak

3

If your ID values are unique, you can simply check to see if the max(id) = min(id). If so, then use either one, otherwise you can return null. Like this:

Select Case When Min(id) = Max(id) Then Min(id) Else Null End As Id,
       Value, Count(*) As [Count]
From   YourTable
Group By Value

Since you are already performing an aggregate, including the MIN and Max function is not likely to take any extra (noticeable) time. I encourage you to give this a try.

2012-04-04 22:21
by G Mastros
This is far better than my ugly union. I applaud you sir. Is the case a potential performance hit on large queries - Chris Carew 2012-04-04 22:35
@CrisCarew I checked the performance compared to your suggestion, and this performs better. You see, I'm only hitting the table once. SQL Server must perform the count aggregate for each group. Asking it to perform the min and max aggregate along the way doesn't affect performance too much. The case statement is only applied once all the aggregates are performed, so once per group - G Mastros 2012-04-04 22:38
I figured as much. Cool beans. I learned something by answering something today - Chris Carew 2012-04-04 22:40
Excellent, thanks - feronovak 2012-04-04 22:41

0

When using group by, after the group by statement you can use a having clause.

So

SELECT [ID]
FROM table
GROUP BY [VALUE]
HAVING COUNT(*) = 1

Edit: with regards to your edited question: this uses some fun joins and unions

CREATE TABLE #table
(ID int IDENTITY,
VALUE varchar(3))

INSERT INTO #table (VALUE)
VALUES('abc'),('abc'),('def'),('abc'),('abc'),('abc')

    SELECT * FROM (
    SELECT Null as ID,VALUE, COUNT(*) as [Count]
    FROM #table
    GROUP BY VALUE
    HAVING COUNT(*) > 1
UNION ALL
    SELECT t.ID,t.VALUE,p.Count FROM
    #table t
    JOIN
    (SELECT VALUE, COUNT(*) as [Count]
    FROM #table
    GROUP BY VALUE
    HAVING COUNT(*) = 1) p
    ON t.VALUE=p.VALUE
) a



DROP TABLE #table
2012-04-04 22:00
by Chris Carew
sorry, edited page to see what should result look lik - feronovak 2012-04-04 22:05
Nope. The ID column would have to be grouped or aggregated in order to be valid in the select list - KeithS 2012-04-04 22:08
@KeithS an easy fix with a join. Edited to add a correct solution - Chris Carew 2012-04-04 22:29

0

The way I would do it would indeed be a CTE:

using @group AS (SELECT value, Count(*) as count from MyTable GROUP BY value HAVING count = 1)
SELECT MyTable.ID, @group.value, @group.count from MyTable
   JOIN @group ON @group.value = MyTable.value
2012-04-04 22:05
by KeithS
sorry, edited page to see what should result look lik - feronovak 2012-04-04 22:06
Edited to have the correct select list - KeithS 2012-04-04 22:10

-1

maybe not the most efficient but something like this works:

SELECT MAX(Id) as ID,Value FROM Table WHERE COUNT(*) = 1 GROUP BY Value
2012-04-04 22:00
by Thorgeir
Ads