SQL - SUM Values based on condition

sql sum column based on criteria
sql sum where
sql sum group by
sql total row
sql sum in where clause
sql sum group by multiple columns
sql sum multiple rows
sql query to sum two columns values based on its record

I am having trouble loading this table. I want all Quantity values <500 summed where the NameCode is the same. If the sum is still less than 500, don't insert. Is this possible?

CREATE TABLE [dbo].[GovQuantity](
        [NameCode] [varchar](40) NULL,
        [Quantity] [decimal](18,6) NULL,
        )



    INSERT INTO GovQuantity
    SELECT  d.NumNameCode
        , Quantity = CASE WHEN QUANTITY < 500 THEN SUM(Quantity) ELSE Quantity END 
          FROM [...]              
    GROUP BY NumNameCode, Quantity

Currently have this:

 NameCode     Quantity
 ArmyGuard       125
 ArmyGuard       605
 ArmyGuard       65
 Boeing          420
 Boeing          750
 Boeing          100

Need:

NameCode     Quantity
 ArmyGuard       605
 Boeing          520
 Boeing          750

The safest way to write this is with union all:

select NameCode, sum(Quantity) as Quantity
from govquantity
where Quantity < 500
group by NameCode
having sum(Quantity) >= 500
union all
select NameCode, Quantity
from govquantity
where Quantity >= 500;

It is tempting to write this using a single aggregation:

I think you simply want:

SELECT d.NumNameCode, SUM(Quantity)
FROM [...]     
WHERE QUANTITY < 500         
GROUP BY NumNameCode,
         (CASE WHEN Quantity >= 500 THEN Quantity END)
HAVING SUM(QUANTITY) >= 500;

However, this will mess up if two quantity values are the same and greater than 500.

If you had a unique id (or at least unique per name), then this would be:

SELECT d.NumNameCode, SUM(Quantity)
FROM [...]     
WHERE QUANTITY < 500         
GROUP BY NumNameCode,
         (CASE WHEN Quantity >= 500 THEN Id END)
HAVING SUM(QUANTITY) >= 500;

Conditional sum in Group By query MSSQL, But I do not know how to conditionally surmise the CopyCost and FullPrice values. Any help would be much appreciated. Regards Freddie. share. Project ID Employee ID Total Days 1 100 1 1 100 1 1 100 2 1 100 6 1 200 8 1 200 2. Now i need this table to look like. Project ID Employee ID Total Days 1 100 10 1 200 10. As iam new to sql,i am little confuse to use SUM() based on above condition.

Perhaps something like this.

This will display individual rows >= 500 and aggregate the misc. others.

Example

;with cte as (
Select NameCode
      ,Quantity
      ,RN       = Row_Number() over (Partition By NameCode Order by Quantity)
 From  [dbo].[GovQuantity]
)
Select NameCode
      ,Quantity = sum(Quantity)
 From  cte
 Group By NameCode,case when Quantity >=500 then RN else 0 end
 Having sum(Quantity) > 500
 Order by NameCode

Returns

NameCode    Quantity
ArmyGuard   605
Boeing      520
Boeing      750

SQL Server SUM() Function By Practical Examples, How can I sum column values with same ID in SQL? SQL SUM() with where . In the following example, we have discussed usage of WHERE clause along with the SQL SUM() function to sum one or more columns against one or more conditions. Example: To get the total SUM of 'advance_amount' of the 'orders' table with the following condition - 1. 'agent_code' must be 'A003',

you can try this.

DECLARE @T TABLE (NameCode  VARCHAR(10), Quantity INT)
INSERT INTO @T VALUES

('ArmyGuard', 125),
('ArmyGuard', 605),
('ArmyGuard', 65 ),
('Boeing   ', 420),
('Boeing   ', 750),
('Boeing   ', 100)


SELECT NameCode, SUM(Quantity) Quantity FROM @T
GROUP BY
    NameCode, 
    (CASE WHEN Quantity < 500 THEN 0 ELSE Quantity END)
HAVING 
    SUM(Quantity) >= 500
ORDER BY 
    NameCode

Result:

NameCode   Quantity
---------- -----------
ArmyGuard  605
Boeing     520
Boeing     750

SQL select values sum same ID, in group by clause that you used in select statement. The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. The syntax of the SUM() function is as follows: SUM([ALL | DISTINCT ] expression)

You seems want union all :

select NumNameCode, sum(Quantity) as Quantity
from table t
where Quantity < 500
group by NumNameCode
union all
select NumNameCode, Quantity
from table t
where Quantity >= 500;

How to SUM two fields within an SQL query, The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. In this syntax: ALL instructs the SUM() function to return the sum of all values including duplicates. I have a data set that I need to conditionally sum rows based on another column The logic is as follow If Line, Lot, & Comments are the same then sum all rows ELSE row Line 8 & 9 meet this criteria so I want to see 1 row for 8 (sum 10) and one row for 9 (sum 15) wh_num abs_num comments line lot order_ qty KU22 007227310311 90289804 9 6FA4202088 280642 2.00 KU22 007227310311 90289804 9

Conditional Sums in SQL Aggregate Methods, The sum will then execute to add up all of those 0s and 1s. A 0 will not add anything, so we will effectively end up counting the values which  Adding Column value from Previous Row based on Where Condition. I have a table with symbol, epoch and Volume. The epoch is stored based on Symbol and Epoch in ascending order. I want to get cumulative volume which is sum of volume and prev timestamp and the volume at current timestamp for the same day and for same symbol.

SQL: SUM Function, This SQL tutorial explains how to use the SQL SUM function with syntax and examples. function is used to return the sum of an expression in a SELECT statement. statement below returns the combined total salary of unique salary values  Sum Values based on Another Column with Formula. To use a formula to sum values in Column B based on Column A, you can create a formula based on the SUMIF function. Just do the following steps: #1 select the text values in Column A (A1:A6), press Ctrl +C to copy these values, and paste into another blank column (Column D).

SQL SUM() function, SQL SUM() on specific column example. To get the total SUM of '​advance_amount' of the 'orders' table, the following SQL statement can be  I want to get the sum of 'VoucherQuantity' and I want its results in different fields based on different values of 'VoucherType' (which for example can be 01, 02, 03, or 04) and I want the result to be grouped by 'PartName' AND 'DLName'.

Comments
  • That is extremely odd. You want aggregate and non-aggregate data at the same time? And where did the other 190 for ArmyGuard go?
  • This makes no sense. If you want to sum all quantity values < 500, then how does (ArmyGuard, 605) appear in your output? That appears to be exactly opposite of what you wrote.
  • What if you have a namecode with two values over 500?
  • Yeah I think my logic is wrong.
  • At the very least, I think you need to move the aggregate function outside of the case statement. Something like SUM (case when quantity < 500 then quantity else 0 end).