Query for finding records where count is only 1-CodePudding

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 |
|       16743289    |     0    |
|       1663890     |     0    |
|       9033175     |     0    |

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.

CodePudding user response：

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

CodePudding user response：

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