Re: still dont' get how to group by

To add a bit - hope not confusion - to the concept of GROUP BY -

GROUP BY is used for things like getting total sales for each sales rep. BTW, the table shown here doesn't seem to have much of anything that could be used for that kind of thing, other than COUNT(*), say, of ITEM#.

But if you have a sales table, or maybe invoices, you could have something like this -

SELECT SALESREP, SUM(INVAMOUNT) from INVOICES GROUP BY SALESREP

What is done, basically, is all the records for each SALESREP are bunched together (grouped), then the total of invoice amount in each record is calculated, and that's the result you get.

HTH
Vern

On 12/31/2013 9:42 AM, Hoteltravelfundotcom wrote:

OK i see I was thinking it was more of a
'control' group by, yes they are fields
CUSTOMER# DTAWHS CHARACTER 8
NAME DTAWHS CHARACTER 40
CITY DTAWHS CHARACTER 30
STATE DTAWHS CHARACTER 3
COUNTRY DTAWHS CHARACTER 3
ZIP_CODE DTAWHS CHARACTER 10
RES_FLAG DTAWHS CHARACTER 1
ZONE DTAWHS CHARACTER 1
ITEM# DTAWHS CHARACTER 15
PROD_LINE DTAWHS CHARACTER 3
ITEM_TEXT DTAWHS CHARACTER 40
ITEM_WT DTAWHS NUMERIC 7 4
ORDER# DTAWHS CHARACTER 8


On Tue, Dec 31, 2013 at 10:34 AM, Norm Dennis <nhdennis@xxxxxxxxxxx> wrote:

GROUP BY is field level order of data.
It looks like you may also have a typo the GROUP BY CUSTOMERr#


Norm Dennis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Tuesday, 31 December 2013 11:04 PM
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.



-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4259 / Virus Database: 3658/6961 - Release Date: 12/30/13

--
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.