locked
"Group By"/Memo conflict (VFP 9.0 sp2) RRS feed

  • Question

  • I have a table with a Memo field that I need to include in a report so it has to be in the Select statement. However, I also have to group the records by another field. A Memo field may not be in a Group By statement but the Group By statement must contain all non-aggregate fields selected. It would appear that I can't group records if one of the fields in the report is a memo. What am I missing? Thanks.
    Monday, November 21, 2011 1:13 PM

Answers

  • You're not missing anything. You're confronted with two restrictions, which make such a select impossible.

    The solution is to not include the memo field in the first place and then join it to the result.

    Bye, Olaf.

    Edit after Tamars post:

    That is one way to join from a parent record after grouping without the memo. If you only have a single table with memo you would need to do a self join on some of the values remaining after aggregation.

    And moreover: In general you don't need a single SQL Select to prepare data for a report and you can also group within the report and aggregate by using report variables instead. Especially if memo data is different for each record of a group of records you may want to print each memo value anyway and print a sum, average or whatever, which  you can calculate via report variable on the fly during printing.


    • Edited by Olaf Doschke Monday, November 21, 2011 10:04 PM
    • Proposed as answer by Mike Feng Wednesday, November 23, 2011 5:59 PM
    • Marked as answer by Mike Feng Monday, November 28, 2011 3:27 PM
    Monday, November 21, 2011 1:41 PM
  • If you think about what GROUP BY does, you can see why including a memo field doesn't make sense. From which record should the memo field be pulled?

    If what you actually have is a parent-child situation where you're grouping on the parent's primary key and the memo is a parent field, then something like this will work:

     

    SELECT Aggregate.*, Parent.TheMemo ;
      FROM Parent ;
        JOIN (SELECT Parent.PK, <aggregate functions on Child> ;
                FROM Parent ;
                  JOIN Child ;
                    ON Parent.PK = Child.ParentPK ;
               GROUP BY Parent.PK) Aggregate ;
          ON Parent.PK = Aggregate.PK

    This assumes you have VFP 9.

    Tamar

    • Proposed as answer by Mike Feng Wednesday, November 23, 2011 5:59 PM
    • Marked as answer by Mike Feng Monday, November 28, 2011 3:27 PM
    Monday, November 21, 2011 9:40 PM
    Answerer

All replies

  • You're not missing anything. You're confronted with two restrictions, which make such a select impossible.

    The solution is to not include the memo field in the first place and then join it to the result.

    Bye, Olaf.

    Edit after Tamars post:

    That is one way to join from a parent record after grouping without the memo. If you only have a single table with memo you would need to do a self join on some of the values remaining after aggregation.

    And moreover: In general you don't need a single SQL Select to prepare data for a report and you can also group within the report and aggregate by using report variables instead. Especially if memo data is different for each record of a group of records you may want to print each memo value anyway and print a sum, average or whatever, which  you can calculate via report variable on the fly during printing.


    • Edited by Olaf Doschke Monday, November 21, 2011 10:04 PM
    • Proposed as answer by Mike Feng Wednesday, November 23, 2011 5:59 PM
    • Marked as answer by Mike Feng Monday, November 28, 2011 3:27 PM
    Monday, November 21, 2011 1:41 PM
  • If you think about what GROUP BY does, you can see why including a memo field doesn't make sense. From which record should the memo field be pulled?

    If what you actually have is a parent-child situation where you're grouping on the parent's primary key and the memo is a parent field, then something like this will work:

     

    SELECT Aggregate.*, Parent.TheMemo ;
      FROM Parent ;
        JOIN (SELECT Parent.PK, <aggregate functions on Child> ;
                FROM Parent ;
                  JOIN Child ;
                    ON Parent.PK = Child.ParentPK ;
               GROUP BY Parent.PK) Aggregate ;
          ON Parent.PK = Aggregate.PK

    This assumes you have VFP 9.

    Tamar

    • Proposed as answer by Mike Feng Wednesday, November 23, 2011 5:59 PM
    • Marked as answer by Mike Feng Monday, November 28, 2011 3:27 PM
    Monday, November 21, 2011 9:40 PM
    Answerer