#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    77
    Rep Power
    9

    Help with a SQL string


    Can somebody tell me what I am doing wrong with this string?
    I am trying to get % of records where stat=1 by each store number. I keep getting invalid token errors.


    Code:
    select store, sum(b.PO) / sum(c.TOT) as PERPAID
    from MYTABLE a
    left join (select store,count(*) as PO from MYTABLE where stat = 1)b on b.store=a.STORE 
    left join (select store,count(*) as TOT from MYTABLE)c on c.store=a.STORE 
    group by store


    Thanks for any help!
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,131
    Rep Power
    2011
    How does it look if you try to specify which store you want to use?
    Code:
    select a.store, ....
    ....
    group by a.store
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    77
    Rep Power
    9
    Still get the same message:

    SQL Message : -104
    Invalid token

    Engine Code : 335544569
    Engine Message :
    Dynamic SQL Error
    SQL error code = -104
    Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)



    Originally Posted by MrFujin
    How does it look if you try to specify which store you want to use?
    Code:
    select a.store, ....
    ....
    group by a.store
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    242
    Rep Power
    17
    Code:
    select 
    store, 
    sum(b.PO) / sum(c.TOT) as PERPAID
    
    from 
    MYTABLE a
    left join (select store,count(*) as PO from MYTABLE where stat = 1 group by 1)b on b.store=a.STORE 
    
    left join (select store,count(*) as TOT from MYTABLE group by 1)c on c.store=a.STORE 
    group by store
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    77
    Rep Power
    9
    Thanks! That worked!

IMN logo majestic logo threadwatch logo seochat tools logo