Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Aggregate sub query help

    Hey Guys,

    I'm having a hard time figuring out this problem... I usually write 2 queries for something like this but am feeling adventurous today. I have simplified my code to where the error is:

    Description:
    my website had a sale on '2013-04-25' and I want to see how many customers made purchases and of those customers how many new customers there were (startdate = '2013-04-25')
    *I am looking to do this in the subquery if its possible.


    select
    count(distinct(p.accountnumber)) as all_buyers,
    count(distinct(select distinct cc.accountnumber from customers cc
    where cc.startdate = '2013-04-25'
    and cc.accountnumber = p.accountnumber)) as new_buyers
    from purchases p
    join customers c on p.accountnumber = c.accountnumber and c.websitecode = 10
    where p.datesettled = '2013-04-25'
    go

    >[Error] Script lines: 1-10 -------------------------
    An expression starting with "ACCOUNTNUMBER" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.. SQLCODE=-119, SQLSTATE=42803, DRIVER=3.53.71 


    Thanks in advance for the help!
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    select
    count(distinct(p.accountnumber)) as all_buyers,
    count(distinct case when c.startdate = '2013-04-25' then c.accountnumber else null) as new_buyers
    from purchases p
    join customers c on p.accountnumber = c.accountnumber and c.websitecode = 10
    where p.datesettled = '2013-04-25'
    Andy

Posting Permissions

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