CODEWITHSUNDEEP
sqloracle-database
pRieS
pRieS

Reputation: 11

Query for finding records where count is only 1

I have a table named discounts. Within this table I have a account number, discount number, a discount status, a discount start and discount end date. I would like to find all records for account numbers with count of discount as 1 and that discount has end dates not equal to zero.

This is how the table looks like:

| account no        | discount no | status   |  end date |
|       14188971    |        111  | 1        | 12-DEC-23 | 
|       14188971    |        111  | 1        |      0    |
|       16743289    |        111  | 1        |      0    |
|       19908543    |        111  | 1        | 14-DEC-23 |
|       2510987     |        111  | 1        | 14-DEC-23 |
|       1663890     |        111  | 1        |      0    |
|       9033175     |        111  | 1        |      0    |

The result should look similar to this:

| account no        | end date  |
|       19908543    | 14-DEC-23 |
|       2510987     | 14-DEC-23 |

Query should exclude accounts with duplicate end dates such as 14188971 which have 2 entries, one of which has end date zero.

I tried the below

select 
      account_no, 
      condition 
   from 
      ( select 
              at.account_no, 
              case when count( case when d.discount_no = '111' 
                                    and d.status = 1 then 1 end) = 1 
                   then 'One active Discount' end as condition
          from
             discounts d
                right join account_t at
                   on at.id = d.account_no
          where 
                 at.account_no in ('14188971','16743289', '19908543', '2510987','1663890','9033175')
             and pt.purchase_end_t <> 0
          group by 
             at.account_no )
   where 
      condition = 'One active Discount'

Expecting query to return just accounts with end dates not equal to zero excluding duplicates which have may have end dates not equal to zero.

Upvotes: 0

Views: 85

Answers (2)

Paul W
Paul W

Reputation: 11274

If all you need is the account number and end date, you can simply use GROUP BY with HAVING:

select account_no,
       MAX(end_date) end_date
  from table
 GROUP BY account_no
 HAVING COUNT(*) = 1
    AND MAX(end_date) IS NOT NULL -- or != 0, or whatever...

If you need other columns, however, you can use windowing functions:

select *
  from (
    select col1,col2,col3,
           COUNT(*) OVER (PARTITION BY account_no) cnt,
           MAX(end_date) OVER (PARTITION BY account_no) end_date
      from table
) where cnt = 1 and end_date IS NOT NULL

Upvotes: 1

dipesh pokhrel
dipesh pokhrel

Reputation: 17

you can easily get your result by following query:

    select accounts.* from accounts inner join (select account_no, count(end_date) as end_date from discounts where status = 1 and end_date != 0 group by account_no) as dis ON dis.account_no = accounts.id

Upvotes: 0

Related Questions