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

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0

    Aggregates and subqueries... OH MY!



    SELECT
    `A`.`MID` as `CreditMID`,
    AVG(`Sale_No`) as `Trans`,
    AVG(`Sale_Amount`) as `TotalVolume`,
    AVG(`Gross_Revenue`) as `Fees`,
    AVG((SELECT SUM(`Volume`) FROM `raw_elavon_icqual` as `B` WHERE `DiscountTypeCode` = 'Q' AND `A`.`MID` = `B`.`MID` GROUP BY `B`.`MID`, `B`.`Period`)) as `QVolume`,
    AVG((SELECT SUM(`Volume`) FROM `raw_elavon_icqual` as `C` WHERE (`DiscountTypeCode` = 'N' OR `DiscountTypeCode` = 'P') AND `A`.`MID` = `C`.`MID` GROUP BY `C`.`MID`, `C`.`Period`)) as `NQVolume`
    FROM `raw_elavon_merchantsummary` as `A`
    GROUP BY `A`.`MID`,`A`.`Period`;


    I am getting the error: 1242. Subquery returns more than 1 row. However, that is exactly what I do want from the subquery, yet at the same time be able to average out the returned one-column results.

    The `raw_elavon_icqual` table is a relatively small table identifying specific clients to each period (monthly) across their time. Each period for each client can have multiple records and I am attempting to pull a SUM of `Volume` for each client, then average that out.

    The `raw_elavon_merchantsummary` table is similar, but for each period there is only one record for each client. However, this table has over a 100 columns for each record.

    Unfortunately I am not able to provide the full details of the tables due to their confidential nature to the purpose at hand. But, can anyone see anything that might stand out as to why this could possibly be? When I attempt to make the subqueries use AVG(SUM(`Volume`)), instead of attempting to AVG() the entire subquery I instead get the error 1111. Invalid use of group function... which doesn't overly surprise me, I was just hoping for the best when I tried that.

    ANY help would be greatly appreciated! I just don't want to have to pull the initial data from the `raw_elavon_merchantsummary` table, then using the returned data have to go back and run the subqueries completely separately... which I can do, I would just prefer not to. I've done a search, but I can't even seem to find examples of anything similar... lots of aggregate examples, tutorials, etc. but nothing showing proper use like this in a more advanced query.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,934
    Rep Power
    379
    I'm always impressed by anyone who can keep track of these kinds of correlated subqueries - but maybe that's the problem!

    If it was me, and I was asking for help in this forum, I'd provide some sample tables and data (in the form of a set of CREATE and INSERT statements) together with the output I'd expect from my query - all nicely bound up within [ code ] tags rather than [ mysql ] tags!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0
    Well to be honest this is the first time I have used this forum so the specific tags to be used in providing examples is an entire matter unrelated to my question; seeing MySQL tags, which is not a common feature amongst MOST forums, I assumed these tags have been provided for very good reason... though apparently not.

    I appreciate the suggestion for future posts in regards to posting topics, however, if you had taken the time to read my ENTIRE post previously you would have read the part that says, and may I "quote" myself: "...I am not able to provide the full details of the tables due to their confidential nature to the purpose at hand." This was stated for good reasons as I probably could get sued by providing CREATE and/or INSERT statements associated with these tables.... in order to find help, WITHOUT BEING SUED, I have asked for people to look at the AVG situation associated with the subqueries and the error being experienced... this should have little to do with the actual data being queried in any regard.

    FOR THOSE WHO NEED LAMANS FOR MY ISSUE:
    1) My subqueries return more than one record, as I desire.
    2) I am attempting to average out those results from the subqueries utilizing the AVG aggregate.
    3) Instead of working, I get the error "Subquery returns more than 1 row." AGAIN, I do want this as a result, then to average that result out. Intention is to do this without the need for multiple queries.

    Specific to cafelatte: If you are not going to provide actual assistance, please refrain from commenting on threads to which you have no real useful information to add to the subject at hand. You waste my time, by making me think I have actually received a response, and you waste yours ... though I doubt you care about yours, I do actually care about mine.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,934
    Rep Power
    379
    Baloney. You're entirely capable of simulating a small but representative data set without compromising confidentiality.

    While I sympathise at your frustration at not being able to solve your problem, your decision to adopt a stance that is both rude and disingenuous is unlikely to improve your chances of a speedy response, but good luck anyway.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,388
    Rep Power
    392
    Code:
    select A.MID as CreditMID,
           avg(Sale_No) as Trans,
           avg(Sale_Amount) as TotalVolume,
           avg(Gross_Revenue) as Fees,
           (select avg(v)
              from (select sum(Volume) as v 
                      from raw_elavon_icqua as B 
                     where DiscountTypeCode = 'Q' 
                       and A.MID = B.MID 
                     group
                        by B.MID, 
                           B.Period) dt) as QVolume,
           (select avg(v)
              from (select sum(Volume) as v
                      from raw_elavon_icqual as C 
                     where DiscountTypeCode in ('N','P')
                       and A.MID = C.MID 
                     group
                        by C.MID,
                           C.Period) dt) as NQVolume
      from raw_elavon_merchantsummary as A
     group
        by A.MID,
           A.Period

IMN logo majestic logo threadwatch logo seochat tools logo