Member Avatar for sofien_1

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
  • 197 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

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'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.