Results 1 to 14 of 14
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: how to get this result?

    Table1
    Code:
    Product    quanity
    A              50
    B              22
    D              11
    C              31
    B              43
    D              8
    C              13
    A              43
    1- How can we get the following table/query from the about table. As you see the sum of each item is shown.
    Query1
    Code:
    Product         quantity_sum
    A                     93
    B                     65
    C                     44
    D                     19
    2- Imagine, that because of any reason, the sum of the quantities of each item should be deducted from item D. can we get the following table/query?
    Query 2
    Code:
    Product    quantitysum   D-deduction     result
    A              93            19             74
    B              65            19             46
    C              44            19             25
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    1
    Code:
    select product,sum(quantity) from mytable
    group by product
    d-deduction comes from where?
    if you want to subtract the sum of d from the sum of the others then use a subquery to retrieve the sum of d
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    132
    thank you very much,
    yes. the sum of d is going to be subtracted from each sum of others. however i think the field D-deduction is not required.
    could you also help me to write the code of the sub-query? i know very less about it.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Possible solution:

    Code:
       select t11.product, SUM(t11.qty), cte.dqty, SUM(t11.qty) - cte.dqty
       from #t1 t11
       inner join 
       (    
           select product, SUM(qty) dqty
             from #t1 t1
             where t1.product = 'D'
            group by t1.Product
       ) cte on cte.Product = 'D'
       where t11.Product <> 'D'
      group by t11.Product, cte.dqty

  5. #5
    Join Date
    May 2012
    Posts
    132
    thank you,
    i pasted it in SQL of the query, but it says " syntax error in FROM clause" pointing to # in the second line.
    what shall i do?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by ariansman View Post
    i pasted it in SQL of the query, but it says " syntax error in FROM clause" pointing to # in the second line.
    what shall i do?
    You provided no DDL to indicate what your table or column names are. So I had to create my own names.

    It will be up to you to make the proper substitutions from the posted solution to your query.

  7. #7
    Join Date
    May 2012
    Posts
    132
    thank you,
    i think i named each table and field in the question,
    sorry i dont know what is DDL. i also don't know what t11, t1 and cte stand for, to be able to substitute them with proper respective names. it only seems to me that qty is a name for quantity, isnt it?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As txxx is an alias for a table name. Yoyd use it if the table name was very long or if you need to use the same table more than once in a single query

    As linksup has suggested YOU need to replace YOUR table and column names for the same values he supplied.
    DDL is the subset of SQL that handles defining the db the table design and so on... See googke fir details
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2012
    Posts
    132
    healdem,
    what you firstly replied to me was great and it totally worked for me as a beginner. isimply pasted t hem, and that was all.
    but for the second question i am thinking if t1 stands for table1? and what does t11 does? as there is no table11. besides i still cant figure out what do cte and dqty stand for.
    thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    t1 and t11 and two different aliases for the same table. This is required if you are using the same table more than once in a given query.
    DDL is Data Definition Language - the SQL statements that create, amend and remove tables and indexes within a database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need this to be done solely within a query or could you do the subtraction in the form or report. after all the first query will supply you with the sum of each product type.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2012
    Posts
    132
    Quote Originally Posted by healdem View Post
    do you need this to be done solely within a query or could you do the subtraction in the form or report. after all the first query will supply you with the sum of each product type.
    yes, the subtraction can be done in either query and/or report.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well one way to ahciueve what you want is to have 3 queries
    1 sum all products (QSumByProduct)
    Code:
    SELECT TABLE1.product, Sum(TABLE1.quantity) AS SumOfProduct
    FROM TABLE1
    GROUP BY product;
    2 sum all D product (QSumofD)
    Code:
    SELECT sum(quantity) AS SumOfD
    FROM TABLE1
    WHERE product = "D";
    3 merge queries 1 and 2
    Code:
    SELECT QSumByProduct.product, QSumByProduct.SumOfProduct, [SumOfProduct]-[SumofD] AS SumOfProductLessD
    FROM QSumByProduct, QSumofD
    ORDER BY Product;
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2012
    Posts
    132
    thank you very much, it was really helpful
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

Posting Permissions

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