RE: SQL group want only one of each group

From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Booth Martin
Sent: Friday, August 18, 2017 1:33 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL group want only one of each group

Thanks.

Using your example, I want to loop through the file and create a

just the colors. If there are 300 records but only 7 colors, I want a

rows.



On 8/18/2017 3:26 AM, Paul Bailey wrote:

What do you want from the query? What does your query look like?

For example, to get the count of rows with the same colour:

select colour, count(*) from theFile group by colour; (replace

with size or style as required)

Because each colour, style and size has a unique stock number, if

want a list of stock numbers then a group by will just give you

row because every row will be unique.


-Paul.


On 18 August 2017 at 07:21, Booth Martin <booth@xxxxxxxxxxxx> wrote:

Scenario: I have a product with 5 colors, 6 styles, and 7 sizes.
Every color has some of every style and every size. Every style

some of every color and every size. every size has some of every
color and every style. Each permutation has a unique Stock number.

I want 3 lists: group by color( 5 rows), group by style(6 rows),
group by
size(7 rows).

I can get the reports, correctly grouped, but I get as many rows as
there are records in the file. I can not figure out how to get

the first of each group.



--
This is the RPG programming on the IBM i (AS/400 and 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

moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries)

mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To

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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD