When you say group by some list of fields you only get one record for the group by fields so any other field has to be an aggregate. In your example you group by customer which means you will only get one record for each customer. When you have a field in your select list such as item#, it does not know which item# to put there because you may have many records for that one customer which are being combined into one. Usually with a group by you will have fields that are a sum or avg or count which are summarizing values for that one customer. Without knowing what you are trying to accomplish it is hard to tell if you need more fields in the group by clause or if you need to remove fields from the select list or some combination of the above.
Scott
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Tuesday, December 31, 2013 8:04 AM
To: Midrange Systems Technical Discussion
Subject: still dont' get how to group by
Why does this group by not work, what is it I need to do?
Column NAME or expression in SELECT list not valid.
CREATE VIEW PROJ_CRWN1/DATA_WHSV2 AS SELECT CUSTOMER#, NAME, STATE, ITEM#, PROD_LINE, INVOICE#, ORD_DATE, INV_DATE, ORD_TYPE, CUST_TYPE, QUANTITY, EXTENDED$ FROM Proj_Crwn1.DTAWHS GROUP BY CUSTOMERr#
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
