# Thread: how to get this result?

1. Registered User
Join Date
May 2012
Posts
137

## 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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

3. Registered User
Join Date
May 2012
Posts
137
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.

4. Registered User
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. Registered User
Join Date
May 2012
Posts
137
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?

6. Registered User
Join Date
Dec 2012
Location
Logan, Utah
Posts
163
Originally Posted by ariansman
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. Registered User
Join Date
May 2012
Posts
137
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

9. Registered User
Join Date
May 2012
Posts
137
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

10. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,081
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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.

12. Registered User
Join Date
May 2012
Posts
137
Originally Posted by healdem
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well one way to ahciueve what you want is to have 3 queries
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;```

14. Registered User
Join Date
May 2012
Posts
137
thank you very much, it was really helpful

#### Posting Permissions

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