Re: Simple SQL Total question

With aggregate functions (ie SUM) all of your fields in the select
statement need to be an aggregate or specified in the GROUP BY clause.

SELECT ALL
T01.IAPRT#, T01.IA101, SUM(T02.IDSHP#) AS SUMIDSHP#, T02.IDDOCD,
T01.IARCC4, T01.IAPRLC, T01.IARCC9
FROM ASTDTA.ICPRTMIA T01 INNER JOIN
ASTDTA.OEINDLID T02 ON IAPRT# = IDPRT#
WHERE IAORDF = '1'
AND IARCC4 IN ('FIN', 'PRT', 'NO')
GROUP BY T01.IAPRT#, T01.IA101, T02.IDDOCD,
T01.IARCC4, T01.IAPRLC,

This should work, but may not be the grouping you want.



On Fri, Dec 13, 2013 at 9:53 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

I get an error message: "Column IA101 or expression in SELECT list not
valid. ""


create view PLEXT1 as
SELECT ALL
T01.IAPRT#, T01.IA101, SUM(T02.IDSHP#) AS SUMIDSHP#, T02.IDDOCD,
T01.IARCC4, T01.IAPRLC,
T01.IARCC9
FROM ASTDTA.ICPRTMIA T01 INNER JOIN
ASTDTA.OEINDLID T02
ON IAPRT# = IDPRT#



WHERE IAORDF = '1'
AND IARCC4 IN ('FIN', 'PRT', 'NO')

GROUP BY T01.IAPRT#


On Fri, Dec 13, 2013 at 12:45 PM, Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx>wrote:

select part#, sum(qtyshipped) from yourfile group by part#

On 12/13/2013 10:52 AM, Hoteltravelfundotcom wrote:

I have been unable to sum data in SQL. I am trying to create a View

that

takes all detail qty for an item and creates summary columns

PART# QTYSHIPPED INVOICE

123 1 12345
123 1 12346
123 1 123457
123 1 123458
123 1 123459

I want to create

Part# = 123
QTYSHIPPED = 5

dont care about invoice.

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

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