CODEWITHSUNDEEP
sqlsql-server
Sami
Sami

Reputation: 4006

sql Select id that matches combination of column

I have a token table

  id | status
  ------------
  1  | taken
  1  | used
  1  | deleted
  2  | taken
  2  | deleted
  3  | taken

I need to count how many tokens are used ( in use or used). If a token is taken and deleted without being used then it should not be counted.

So sql would be sth like

 SELECT count(*) if the id's status is not (taken & deleted)

The desired number of used token in above example is 2 as

 id 1 has been taken used and deleted -> count it
 id 3 has been taken -> count it
 id 2 has been taken and deleted without being used -> do not count it

Upvotes: 2

Views: 195

Answers (6)

Sami
Sami

Reputation: 4006

Coming back to this question, one solution could be with using Pivot

SELECT COUNT(id)
FROM (
     SELECT id, status FROM Token
) src
PIVOT
(
    COUNT(status) FOR status IN ([taken], [used], [deleted])
) pvt
WHERE (taken = 1 AND deleted = 0)OR (used = 1)

DEMO

Upvotes: 0

Okdel
Okdel

Reputation: 183

if token has been taken and used -> do not count it

SELECT
  SUM(DECODE(status, 'taken', 1, 0)) +
  SUM(DECODE(status, 'used', 1, 0)) -
  SUM(DECODE(status, 'deleted', 1, 0))
FROM
  token t
WHERE
  status <> 'used' OR
  EXISTS(SELECT 1 FROM token t2 WHERE t2.id = t.id and t2.status = 'deleted')

if token has been taken and used -> count it

SELECT
  COUNT(1)
FROM
  token t
WHERE
  status = 'taken' AND
  (
    EXISTS(SELECT 1 FROM token t2 WHERE t2.id = t.id and t2.status = 'used') OR
    NOT EXISTS(SELECT 1 FROM token t2 WHERE t2.id = t.id and t2.status = 'deleted')
  )

Upvotes: 0

Jason Whitish
Jason Whitish

Reputation: 1438

You need to be able to take into account all three conditions, so a naive approach would be to just compare each three with a case statement:

WITH grouped as
(
    select id from #uses group by id
)
select grouped.id, 
    used = 
        CASE WHEN used.id is not null THEN 'YES' 
            WHEN taken.id is not null and deleted.id is null THEN 'YES'
        ELSE 'NO'
        END
from grouped
left join #uses taken on grouped.id = taken.id
    and taken.use_status = 'taken'
left join #uses used on grouped.id = used.id
    and used.use_status = 'used'
left join #uses deleted on grouped.id = deleted.id
    and deleted.use_status = 'deleted'

The case statement will stop whenever the condition is met, so you only need to WHEN's and an ELSE to meet the conditions.

This is a naive approach, though, and assumes that you only ever have one row per id and use status type. You'd have to do some additional work if that wasn't the case.

Upvotes: 0

Milen
Milen

Reputation: 8877

Try this:

SELECT SUM(CASE WHEN (CHARINDEX('used', data.status) > 0) OR (data.status = 'taken') THEN 1 ELSE 0 END) as [count]
FROM 
(
   SELECT DISTINCT id, (SELECT STUFF((SELECT Distinct ',' + status
                                          FROM        token a
                                             WHERE a.id = b.id
                                          FOR XML PATH (''))
                                     , 1, 1, '')) as status
    FROM token b     
) data

Demo

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460208

A little bit verbose but efficient and still readable and maintainable:

SELECT COUNT(DISTINCT id)
FROM dbo.Token t
WHERE EXISTS
(
    SELECT 1 FROM dbo.Token t1
    WHERE t.id = t1.id
    AND   t1.status = 'used'
)
OR
(
  EXISTS(
      SELECT 1 FROM dbo.Token t1
      WHERE t.id = t1.id
      AND   t1.status = 'taken'
  )
  AND NOT EXISTS(
      SELECT 1 FROM dbo.Token t1
      WHERE t.id = t1.id
      AND   t1.status = 'deleted'
  )
)

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Use aggregation and a having clause to get the list of eligible ids:

SELECT id
FROM token t
GROUP BY id
HAVING SUM(case when status = 'taken' then 1 else 0 end) > 0 or
       SUM(case when status = 'used' then 1 else 0 end) > 0;

To get the count, use a subquery or CTE:

SELECT COUNT(*)
FROM (SELECT id
      FROM token t
      GROUP BY id
      HAVING SUM(case when status = 'taken' then 1 else 0 end) > 0 or
             SUM(case when status = 'used' then 1 else 0 end) > 0
     ) t

Upvotes: 0

Related Questions