Re: Paul, do you address this in any of your books?
At 14:40 -0600 3/14/03, DANIEL GADDIS wrote: I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel Actually, if I remember correctly, I don't cover this anywhere. I recommend reading the cross-tabulation article on mysql.com that an earlier response referred to. -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Paul, do you address this in any of your books?
i correct my example : select USERID,IF(DAY='2003-01-02',SUM(LIS_QUANTITY),0) as 2003-01-02,IF(DAY='2003-01-03',SUM(LIS_QUANTITY),0) as 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where > YOUR_CONDITIONS GROUP BY DAY; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Gelu Gogancea" <[EMAIL PROTECTED]> To: "DANIEL GADDIS" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, March 14, 2003 11:05 PM Subject: Re: Paul, do you address this in any of your books? > Hi, > You can try to use IF(),CASE() function in the SQL statement. > > Eg. > > select USERID,IF(DAY='2003-01-02',COUNT(*),0) as > 2003-01-02,IF(DAY='2003-01-03',COUNT(*),0) as > 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where > YOUR_CONDITIONS GROUP BY DAY; > > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "DANIEL GADDIS" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, March 14, 2003 10:40 PM > Subject: Paul, do you address this in any of your books? > > > > I'm running MySql 4.0.10-gamma-max-nt-log > > I have 1 table like the one below... > > ++-+--+ > | DAY| USERID | LIS_QUANTITY | > ++-+--+ > | 2003-01-02 | H0850A1 | 539 | > | 2003-01-02 | LBBSWJR |7 | > | 2003-01-02 | O0600B3 | 21 | > | 2003-01-03 | H0850A1 |6 | > | 2003-01-03 | H2610A1 | 51 | > | 2003-01-03 | O0600B3 | 19 | > | 2003-01-04 | H0850A1 |8 | > | 2003-01-04 | H2610A1 | 13 | > | 2003-01-04 | LBBSWJR |3 | > ++-+--+ > > I would like a sql query to produce output like the following... > > +-++++ > | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | > +-++++ > | H0850A1 |539 | 6 | 8 | > | H2610A1 || 51 | 13 | > | LBBSWJR | 7 || 3 | > | O0600B3 | 21 | 19 || > +-++++ > > Can I do this just by using sql and not adding php, perl, or any > other language? > > If mysql can't handle this with sql only, would it be able to do it > once subselects or other features are available in future releases? > > Anyone used postgresql? I wonder if it could handle it. > > Any thoughts? > > Thanks, > Daniel > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Paul, do you address this in any of your books?
this article on cross tabulations should help you ... http://www.mysql.com/articles/wizard/index.html - hcir (mysql sql query) On Friday, Mar 14, 2003, at 11:40 America/Anchorage, DANIEL GADDIS wrote: I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Paul, do you address this in any of your books?
Hi, You can try to use IF(),CASE() function in the SQL statement. Eg. select USERID,IF(DAY='2003-01-02',COUNT(*),0) as 2003-01-02,IF(DAY='2003-01-03',COUNT(*),0) as 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where YOUR_CONDITIONS GROUP BY DAY; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "DANIEL GADDIS" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 14, 2003 10:40 PM Subject: Paul, do you address this in any of your books? I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Paul, do you address this in any of your books?
I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php