Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010

    Unanswered: conditional counts, over/partition bys


    I need to extract the values of a set of data that I also need to count, but only if there are enough of them, specifically count the shiptos that belong under a set of billtos, but extract the actual shiptos.

    here's what I have
    WITH data (acct, billto, shipto) AS (
      ('1234as', '555', '001')
     ,('1234as', '555', '002')
     ,('1234as', '555', '003')
     ,('3456as', '555', '004')
     ,('3456as', '777', '001')
     ,('3456as', '777', '002')
     ,('3456as', '777', '003')
     ,('3456as', '777', '004')
     ,('3456as', '777', '005')
    ) (
      SELECT acct, billto, shipto, COUNT(*) OVER(PARTITION BY billto) 
      FROM data 
    ) WITH UR
    this will count the shiptos and allow me to extract them, but when i add HAVING or a WHERE condition shown below to limit what rows get returned, it either doesn't limit the rows returned or i get errors.

      SELECT acct, billto, shipto, SUM(CASE WHEN COUNT(*) OVER(PARTITION BY billto)>4 THEN 1 ELSE 0 END) OVER(PARTITION BY billto)
      FROM data
        SELECT acct, billto, shipto, COUNT(*) OVER(PARTITION BY billto) 
      FROM data 
    group bys won't work too well because i need to extract the shiptos themselves. also, because we are talking about millions of rows, i want to avoid having to save the results in a temp table and query that, but if that is the only way, then i guess I have no choice, but I am hoping there is another way.

    please let me know if there is a way to do this. thanks,


  2. #2
    Join Date
    Feb 2008
    Because OLAP specificaions( ... OVER() ) are applied to the resulting select-list.

    You can't use an expression in SELECT clause in WHERE or HAVING clause in same sub-select.
    Please see
    The clauses of the subselect are processed in the following sequence:
    1. FROM clause
    2. WHERE clause
    3. GROUP BY clause
    4. HAVING clause
    5. SELECT clause
    6. ORDER BY clause
    7. FETCH FIRST clause
    DB2 for Linux UNIX and Windows 10.1.0 > Database fundamentals > SQL > Queries > subselect

    If you want to use the result of OLAP specificaions in WHERE or HAVING clause,
    put the OLAP specificaions in a subquery, like...
    SELECT acct , billto , shipto
     FROM  (SELECT s.*
                 , COUNT(*)
                      OVER(PARTITION BY billto) AS count_by_billto
             FROM  data AS s
     WHERE count_by_billto > 4
    Note: I like to put space(es) between language elements and delimiters.
    Because, I experienced many times loss/extra delimiters(e.g. comma, period) and/or confusions of them,
    when debugging program codes including SQL.

  3. #3
    Join Date
    Apr 2010
    thanks tonkuma,

    i wrote it as you have and found the added cost to be immeasurable anyway, even across my large dataset. works fine now.

    also great info about the order of clauses. makes sense now why i cannot do that.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts