Re: SQL question combine 3 queries into 1?

[VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>]

Hi Art


Which solution did you use? Peter's is row-wise, Birgitta's is column-wise - it wasn't explicitly stated in your question which you wanted, although I kind of thought you wanted the 3 values all in a row.


Cheers
Vern

On Tue, 11 Jun, 2024 at 10:37 AM, Art Tostaine, Jr. <atostaine@xxxxxxxxx> wrote:
 

To: midrange systems technical discussion

I did not. That works perfectly thanks Birgitta and Peter.

Art

On Tue, Jun 11, 2024 at 11:33 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx<mailto:Hauser@xxxxxxxxxxxxxxx>>
wrote:

> Did you try something like this?
>
> Select Sum(Case When Substr(Tlc, 1, 1) = '9' Then QTY End),
>             Sum(Case When Substr(Tlc, 1, 1) = '6' Then QTY End),
>             Sum(Case When Substr(Tlc, 1, 1) = '1' Then QTY End)
>     From legmov
>     Where drvrid = '180481089';
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
> Database and Software Architect
> IBM Champion since 2020
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> "Train people well enough so they can leave, treat them well enough so they
> don't want to. " (Richard Branson)
> "Learning is experience … everything else is only information!" (Albert
> Einstein)
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Art
> Tostaine, Jr.
> Sent: Tuesday, 11 June 2024 17:08
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
> Subject: SQL question combine 3 queries into 1?
>
> I have these 3 queries that give me a qty for each type of location.  Is it
> possible to get the value of all 3 sum(qty)'s in 1 query?
>
> Thank you
>
> -- tables
> select *sum*(qty) table from legmov
> where drvrid = *180481089*
> and *substr* (tlc,*1*,*1*) = '9';
> -- problem
> select *sum*(qty) problem from legmov
> where drvrid = *180481089*
> and *substr* (tlc,*1*,*1*) = '6';
>
> *-- Staging*select *sum*(qty) Staging from legmov where drvrid =
> *180481089*
> and *substr* (tlc,*1*,*1*) = '1';
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
> questions.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
> questions.