Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: Count rows based on field in a column not in a group by

    Hello,

    I'm having difficulty counting rows based on a field in a column not in a group by, where the field must be a certain value to be counted. i denormalized the data for readability.

    I need 3 counts, total orders, total orders where the item was added from a saved list, and total number of items added from a saved list. the middle one i'm having difficulty with.

    Code:
    WITH atable (account, ordernum, itemnum, addtocartmethod) AS ( 
       VALUES
        ('111abc', 10001, 554, 'l')
      , ('111abc', 10001, 555, 'l')
      , ('111abc', 10001, 556, 'p')
      , ('111abc', 10002, 432, 'p')
      , ('111abc', 10002, 433, 'p')
      , ('111abc', 10003, 225, 'l')
      , ('222abc', 10004, 432, 'p')
      , ('222abc', 10004, 433, 'p')
      , ('222abc', 10005, 225, 'l')
    ) (
      SELECT 
        account, 
        COUNT(DISTINCT ordernum), 
        SUM(CASE addtocartmethod WHEN 'l' THEN 1 ELSE 0 END) 
      FROM atable 
      GROUP BY account 
    ) WITH UR
    Result of the above is:
    Code:
    111abc	3	3
    222abc	2	2
    Expected result will have the middle counted field, as account 111abc has 2 orders that utilized a saved list to add the item to their cart, and 222abc only has 1 order that utilized a list.

    Code:
    111abc	3	2	3
    222abc	2	1	2
    I tried using OVER PARTITION BYs but it makes me add it to the group by, which doesn't group the rest of the rows correctly.

    if anyone has any idea, i would appreciate it.

    thanks,
    -don

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Would "...GROUP BY ROLLUP (account, ordernum)" do what you need?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2012
    Posts
    7
    The following query should do what you need:

    WITH atable (account, ordernum, itemnum, addtocartmethod) AS (
    VALUES
    ('111abc', 10001, 554, 'l')
    , ('111abc', 10001, 555, 'l')
    , ('111abc', 10001, 556, 'p')
    , ('111abc', 10002, 432, 'p')
    , ('111abc', 10002, 433, 'p')
    , ('111abc', 10003, 225, 'l')
    , ('222abc', 10004, 432, 'p')
    , ('222abc', 10004, 433, 'p')
    , ('222abc', 10005, 225, 'l')
    )
    select account, count(1)
    from(
    select distinct account, ordernum
    from atable
    where addtocartmethod = 'l')
    group by account
    WITH UR

  4. #4
    Join Date
    Apr 2010
    Posts
    32
    I think i got it. I mostly used k_S' strategy, but only because I didn't know about Rollup and need to play around with it some before I know how to use it.

    This query works.
    Code:
    WITH atable (account, ordernum, itemnum, addtocartmethod) AS ( 
       VALUES
        ('111abc', 10001, 554, 'l')
      , ('111abc', 10001, 555, 'l')
      , ('111abc', 10001, 556, 'p')
      , ('111abc', 10002, 432, 'p')
      , ('111abc', 10002, 433, 'p')
      , ('111abc', 10003, 225, 'l')
      , ('222abc', 10004, 432, 'p')
      , ('222abc', 10004, 433, 'p')
      , ('222abc', 10005, 225, 'l')
    ) ( 
      SELECT account, COUNT(ordercount), SUM(CASE WHEN orderlistcount>0 THEN 1 ELSE 0 END), SUM(orderlistcount) 
      FROM ( 
        SELECT 
          account, 
          ordernum, 
          COUNT(ordernum) OVER(PARTITION BY account) AS ordercount, 
          SUM(CASE addtocartmethod WHEN 'l' THEN 1 ELSE 0 END) AS orderlistcount  
        FROM atable 
        GROUP BY account, ordernum 
      ) 
      GROUP BY account 
    ) WITH UR
    I'll spend more time with Rollup because it looks like it will probably open up a whole new branch of abilities for my future queries and is probably easier to write and read than this.

    thanks all for the replies!

    -don

Posting Permissions

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