RE: SQL

[simafrog <SimaFrog@xxxxxxxxxx>]

Thank you Chris and Matt, that is what I am looking for.
Rob400
> -----Original Message-----
> From: chris beck [mailto:cbeck@xxxxxxxxxxxx]
> Sent: Friday, March 19, 2004, 12:05 PM
> To: rpg400-l@xxxxxxxxxxxx
> Cc: SimaFrog@xxxxxxxxxx
> Subject: RE: SQL
> 
> Rob, 
> 
> I think this will do what you want, I was unable to figure out how to do it in
> one statement.
> 
> CREATE TABLE QTEMP.TEMPFILE (ID  INTEGER GENERATED ALWAYS AS IDENTITY , 
>                             FIELD1 CHAR(10)  NOT NULL WITH DEFAULT)
>                                                
> INSERT INTO QTEMP.TEMPFILE (FIELD1) SELECT DISTINCT(FIELD1) FROM
> SOMELIB.SOMEFILE 
> 
> SELECT ' ', ID, FIELD1 FROM QTEMP.TEMPFILE
> UNION
> SELECT 'Total', COUNT(ID) AS ID, ' ' FROM QTEMP.TEMPFILE
> ORDER BY ID
> 
> Chris Beck                                                                    
>                 
> 
> 
> 
> >>> SimaFrog@xxxxxxxxxx 03/19/04 07:00AM >>>
> Matt, The select count gives you a total. What I need is the details and the
> totals on the same output.
> 
> 1. ABC
> 2. DEF
> 
> Total 2
> 
> My main Select is a Select Distinct so I can remove the dups.
> Thank you,
> Rob400
> > -----Original Message-----
> > From: Haas  Matt [mailto:Matt.Haas@xxxxxxxxxxx] 
> > Sent: Thursday, March 18, 2004, 1:33 PM
> > To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> > Subject: RE: SQL
> > 
> > Rob,
> > 
> > Are you trying to get a count of the number of rows? If so you use a query
> > such as:
> > 
> > select count(*) from myfile where somecondition=true
> > 
> > If you're trying to add the values from a particular column (to get a
> total),
> > you'd do something like:
> > 
> > select sum(number_field) from myfile where somecondition=true
> > 
> > If you want these for the entire file, just leave off the where clause.
> > 
> > I don't know that I'd recommend any of the books in the Info Center for
> > learning SQL but there are several good books on the subject and you should
> be
> > able to find them at a book store that carries computer books (or
> Amazon.com).
> > 
> > Matt
> > 
> > -----Original Message-----
> > From: simafrog [mailto:SimaFrog@xxxxxxxxxx] 
> > Sent: Thursday, March 18, 2004 4:15 PM
> > To: RPG programming on the AS400 / iSeries
> > Subject: SQL
> > 
> > 
> > HI I am not sure if this is the right list for an SQL question, as I don't
> see
> > any specific SQL List. 
> > I would like to know how to add a column for each record simply as a
> > sequential number and then getting a total at the end (count). I do not see
> > this anywhere on my STRSQL on the AS/400. If this is the wrong list, please
> > re-direct me to the proper one.
> > Thanks,
> > Rob
> > _______________________________________________
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
> > or email: RPG400-L-request@xxxxxxxxxxxx 
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> > 
> > 
> > _______________________________________________
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
> > or email: RPG400-L-request@xxxxxxxxxxxx 
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> > 
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
> or email: RPG400-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>