CODEWITHSUNDEEP
sqlsql-server
Md Jawad
Md Jawad

Reputation: 1

SQL sum of query

Chemical Name   QTY DOA
-------------   --- --------
Acetic acid     12  24-01-15
2BL             58  22-01-15
Action Viol     22  20-01-15
Balmetenca      74  18-01-15
AWH             13  13-01-15
AWH             22  13-01-15
Actoil          11  07-01-15
Ammonium sulp   12  09-01-15
Actoil 4        11  19-01-15
Actoil ITS      24  06-01-15
Balysyn         35  11-01-15
Bastimol PH     14  12-01-15
Acid Ph         17  05-01-15
Acetic acid     12  24-12-14
2BL             20  01-12-14

Hi, I want to get output as how much qty is present in each chemical_name like chemical_name has qty(13+22) i should be then 25 and rest of chemical as it is.. (i.e) to see total qty in each chemical_name am using below query:

SELECT Chemical_name,
       Qty,
       DOA
FROM   sample_chemical
GROUP  BY Chemical_name,DOA,Qty
HAVING DOA BETWEEN '2015-01-01' AND '2015-01-30'

but it is not summing up. If i use where clause then it is working fine.. if new chemical name is added then we cant use where clause .

Upvotes: 0

Views: 78

Answers (2)

Vitox
Vitox

Reputation: 4424

You don't used the Sum function at all in your query!

What about this:

Select Chemical_name, Sum(Qty), Count(Chemical_name), DOA
From sample_chemical
Where DOA between '2015-01-01' and '2015-01-30'
Group by Chemical_name, DOA

Also, I'll give you a advide: Don't make a so messed question. A lot of users here will just give you a downvote and they will be right because the lack of information about what you need, and what you already tried. Instead, try to make the things as clear as possible, to help who wants to help you understand exactly what are your needs and problems. Delimiting what's your actual data, the structure of your DB/Table, what is exactly the result you want to generate will help a lot. Just try to read your own question but like you don't know your DataBase, what a chemical substance, nor the quantity actually is, and I guess you will understand what I mean.

I also inserted the Count of how many same Chemicals were found (Change to use some Id from sample_chemical table, for better performance). :)

Good luck, and hope I helped!

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

You need to remove the unwanted columns from group by and use sum aggregate in select.

use Chemical_name alone in group by not all the columns, since you want to find the total qty present in each Chemical_name.

SELECT Chemical_name,
       Sum(Qty)
FROM   sample_chemical
WHERE  DOA BETWEEN '2015-01-01' AND '2015-01-30'
GROUP  BY Chemical_name

If you want the total qty in each Chemical_name per day then you need to add Chemical_name and DOA in group by

SELECT Chemical_name,
       Sum(Qty),
       DOA
FROM   sample_chemical
WHERE  DOA BETWEEN '2015-01-01' AND '2015-01-30'
GROUP  BY Chemical_name, DOA

Upvotes: 0

Related Questions