CODEWITHSUNDEEP
sqlgroup-by
orange
orange

Reputation: 740

sql query group

SQL query question

I have a query like 

select proposal_id, service_id,account_type 
from table1

The result is like this:

proposal_id  service_id  account_type
   1          1001        INTERVAL
   1          1002        INTERVAL
   2          1003        NON INTERVAL
   2          1004        NON INTERVAL
   3          1005        NON INTERVAL
   3          1006        INTERVAL

I want to write a query: for each proposal_id, if all the service have INTERVAL then get 'INTERVAL', if all NON-INTERVAL get 'NON-INTERVAL', if both, get 'Both'

For the example above, it should return

proposal_id   account_type
   1           INTERVAL
   2           NON-INTERVAL
   3           BOTH

Upvotes: 1

Views: 149

Answers (4)

dcarneiro
dcarneiro

Reputation: 7180

select proposal_id,
case when count(distinct account_type) > 1 then 'BOTH'
    else max(account_type)
    end
from table1
group by proposal_id

You have the fiddler here.

Upvotes: 0

Morphed
Morphed

Reputation: 3619

Data:

declare @table table (id int, sid int, acc nvarchar(20))
insert @table VALUES (1,1001,'INTERVAL'),(1,1002,'INTERVAL'),(2,1003,'NON INTERVAL'),(2,1004,'NON INTERVAL'),
(3,1005,'NON INTERVAL'),(3,1006,'INTERVAL')

Query:

select x.Id
    ,   CASE counter
            WHEN 1 THEN x.Account_Type
            ELSE 'BOTH'
        END AS Account_Type
from (
    select  Id, Count(DISTINCT(acc)) AS counter, MAX(acc) As Account_Type
    from @table
    GROUP BY Id
) x

Results

Id          Account_Type
----------- --------------------
1           INTERVAL
2           NON INTERVAL
3           BOTH

Upvotes: 3

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

You could use count distinct to determinate if it is both then use CASE to determinate what to display

SELECT DISTINCT proposal.proposal_id, 
       CASE cou 
            WHEN 1 THEN type ELSE 'Both' END as TYPE
FROM proposal
INNER JOIN (SELECT proposal_id, count(distinct type) cou 
            FROM proposal GROUP BY proposal_id) inn 
       ON proposal.id = inn.id

Upvotes: 1

jh0486
jh0486

Reputation: 11

SELECT
      b.proposal_id
     ,CASE
        WHEN s1.proposal_id IS NOT NULL AND s2.proposal_id IS NOT NULL THEN 'BOTH'
        WHEN s1.proposal_id IS NOT NULL THEN 'INTERVAL'
        WHEN s2.proposal_id IS NOT NULL THEN 'NON-INTERVAL'
        ELSE 'UNKNOWN'
     END [account_type] 
FROM table1 b
  LEFT JOIN(
    SELECT proposal_id,account_type FROM table1 WHERE account_type = 'INTERVAL'
    ) s1
      ON b.proposal_id = s1.proposal_id
  LEFT JOIN (
    SELECT proposal_id,account_type FROM table1 WHERE account_type = 'NON-INTERVAL'
    )s2
      ON b.proposal_id = s2.proposal_id

Upvotes: 1

Related Questions