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.

-------------------------------------------------

This email transmission and any documents, files or previous

email messages attached to it may contain information that is

confidential or legally privileged. If you are not the intended

recipient, you are hereby notified that any disclosure, copying,

printing, distributing or use of this transmission is strictly

prohibited. If you have received this transmission in error,

please immediately notify the sender by telephone or return

email and delete the original transmission and its attachments

without reading or saving in any manner.



The Evangelical Lutheran Good Samaritan Society.


---------------------------------------------------------