0

Kindly note that I have the below table, I am trying to spool the count of the sum of the amtpd by grouping the duedt where it is less than or equal to 31-dec-2013, but the amtpd that has the duedt of 31-dec-2013 and that has trndt between 01-dec-2013 and 31-dec-2013, should be less that 10000, in this case the count should be 3.

|duedt|         |amtp|       |trndt|
   |10/31/2013|    |2007.1|     |10/27/2013| 
   |10/31/2013|    |8442.9|     |12/16/2013| 
   |11/30/2013|    |10450|      |12/16/2013|
   |12/31/2013|    |2107.1|     |12/16/2013|
   |12/31/2013|    |8342.9|     |3/23/2014|
   |12/31/2013|    |0|          |5/5/2014|

I am using the below query but it is giving me zero count

 SELECT COUNT (SUM (amtpd))
     FROM ln02rep2
    WHERE trndt <= '31-dec-2013'
    AND (
          (SELECT SUM (amtpd)
                FROM ln02rep2
               WHERE trndt BETWEEN '01-dec-2013' AND '31-dec-2013'
                AND duedt = '31-dec-2013'
              )>='10450.00')
   GROUP BY by duedt;
2
Contributors
1
Reply
13
Views
3 Years
Discussion Span
Last Post by urtrivedi
0

1) your last 2 records transaction date is not in december so it is not in result
2) You can use having clause to check aggregate value instead of querying again

Two sample queries:

select duedt, count(*) cnt , sum(amtpd) from ln02rep2
               WHERE trndt BETWEEN '01-dec-2013' AND '31-dec-2013'
                AND duedt = '31-dec-2013'
group by duedt                






select duedt, count(*) cnt, sum(amtpd) from ln02rep2
               WHERE trndt BETWEEN '01-dec-2013' AND '31-dec-2013'
                AND duedt = '31-dec-2013'
group by duedt                
having sum(amtpd)>='10450.00'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.