Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: CREATE TABLE () ... Select and strange results

    Why in the query result is more column than the declared in the query?
    Code:
    CREATE TABLE FAKTURA (KOD VARCHAR(8),KRAJ VARCHAR(2),WARTOSC DECIMAL(9,2) UNSIGNED,WAGA DECIMAL(5,2) UNSIGNED,ILOSC SMALLINT UNSIGNED)
    SELECT KOD,KRAJ,SUM(WARTOSC),SUM(WAGA),SUM(ILOSC) FROM TEMP GROUP BY KOD,KRAJ
    Result:
    http://vlep.pl/1w5ilx.jpg

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably because your create statement is for table FAKTURA, yet your select is for table TEMP
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Columns from the SELECT statement are appended to the right side of the table.
    But, how to select these new appended columns?
    I can not query SELECT SUM(WARTOSC). Error: Unknown column 'wartosc'. But SELECT KOD or KRAJ is correct.
    Last edited by duf; 07-25-12 at 05:44.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the SHOW TABLES for both tables?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by healdem View Post
    can we see the SHOW TABLES for both tables?
    I do not understand what you mean in this answer. Can you more clearly?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see what columns are in table FAKTURA and table TEMP?
    if you you use a query browser such as MySQL workbench then you should be able toi use the SHOW TABLES command to display the table structure for both tables

    my suspicion is that table TEMP is based on an earlier dataset of FAKTURA.
    I suspect WARTOSC is present in FAKTURA but not in table TEMP
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by healdem View Post
    can we see what columns are in table FAKTURA and table TEMP?
    if you you use a query browser such as MySQL workbench then you should be able toi use the SHOW TABLES command to display the table structure for both tables

    my suspicion is that table TEMP is based on an earlier dataset of FAKTURA.
    I suspect WARTOSC is present in FAKTURA but not in table TEMP
    Your suspicions are not correct. WARTOSC exists in table TEMP
    First i create table temp
    Code:
    CREATE TABLE TEMP ( ..., WARTOSC DECIMAL(9,2) UNSIGNED, ... )
    then table faktura
    Code:
    CREATE TABLE FAKTURA (ID MEDIUMINT PRIMARY KEY AUTO_INCREMENT)
    SELECT KOD,KRAJ,SUM(WARTOSC),SUM(WAGA),SUM(ILOSC) FROM TEMP GROUP BY KOD,KRAJ
    And SELECT SUM(WARTOSC) return Error: Unknown column 'wartosc'. while SELECT KOD or KRAJ is correct.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    right now I don't care about the create statement

    I'd like to see the design of the two tables
    my bad in place of show tables use show columns in a suitable query browser
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i prefer SHOW CREATE TABLE because it includes indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    i prefer SHOW CREATE TABLE because it includes indexes
    Table FAKTURA
    http://vlep.pl/ae7gsd.jpg
    Table TEMP
    http://vlep.pl/lma8zd.jpg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •