SQL Server:Sum() of Multiple Rows Including Where Clauses

SQL Server : SUM() of multiple rows including where clauses

This will bring back totals per property and type

SELECT  PropertyID,
        TYPE,
        SUM(Amount)
FROM    yourTable
GROUP BY    PropertyID,
            TYPE

This will bring back only active values

SELECT  PropertyID,
        TYPE,
        SUM(Amount)
FROM    yourTable
WHERE   EndDate IS NULL
GROUP BY    PropertyID,
            TYPE

and this will bring back totals for properties

SELECT  PropertyID,
        SUM(Amount)
FROM    yourTable
WHERE   EndDate IS NULL
GROUP BY    PropertyID

......

SQL Sum Multiple rows into one

Thank you for your responses. Turns out my problem was a database issue with duplicate entries, not with my logic. A quick table sync fixed that and the SUM feature worked as expected. This is all still useful knowledge for the SUM feature and is worth reading if you are having trouble using it.

Sum multiple rows while writing a query SQL

You group by b.partyID, b.credit, b.total, b.price. This means you get one result row per b.partyID, b.credit, b.total, b.price.

Let's say these are your table's records:

partyID  credit  price  total  date
1        10      20     30     2015-10-01
1        30      20     10     2015-10-02
1        10      20     30     2015-10-03
1        30      20     50     2015-10-04
2        10      20     30     2015-10-01

then you'll get (which you could also have got with DISTINCT, as you are not using any aggregate functions):

GROUP BY clause applied:

partyID  credit  price  total
1        10      20     30
1        30      20     10
1        30      20     50
2        10      20     30

SELECT clause applied:

partyID  opening  debit  credit  closing
1        10        20    0       30
1        30        0     20      10
1        30        20    0       50
2        10        20    0       30

In your SELECT clause you are using subqueries such as (select top 1 b.credit). So you are saying: "Give me one record. Of all these one records (sic) give me the top one by whatever order you like (TOP without ORDER BY). Fill this record with one value. This one value is to be b.credit."

b.credit is in the GROUP BY clause, so there is only one value per group. You can easily replace the whole subquery (select top 1 b.credit) with a mere b.credit.

Here is your query re-written:

SELECT distinct
  b.partyID, 
  b.credit as opening,
  case when b.total > b.credit then b.price else 0 end as debit,
  case when b.total < b.credit then b.price else 0 end as credit,
  b.total as closing
FROM tblPartyOrder b
WHERE b.date >= '2014-03-06' and b.date <= '2016-03-09'
ORDER BY b.partyID;

Now to your actual problem: You want one row per partyID, so group by partyID (only). You want a sum, so use the aggregate function SUM. You want to find the first and last record per partyID, so mark them somehow. You can use the analytic function ROW_NUMBER for this, giving these records the #1:

select
  partyid,
  min(case when first_is_one = 1 then credit end) as opening,
  sum(case when total > credit then price else 0 end) as debit,
  sum(case when total < credit then price else 0 end) as credit, 
  min(case when last_is_one = 1 then total end) as closing
from
(
  select
    po.partyid, po.credit, po.total, po.price,
    row_number() over (partition by po.partyid order by po.date) as first_is_one,
    row_number() over (partition by po.partyid order by po.date desc) as last_is_one
  from tblpartyorder po
  where po.date >= '2014-03-06' and po.date <= '2016-03-09'
) marked
group by partyid
order by partyid;

SQL Sum Multiple Rows Without Group By

Well looks like my comment already solve it, but I was already doing this in case the problem is you didn't know what are the event_id for each date

The trick is select up to 5 events from each loc_name, eventDate In this case I select the ones with more teams. You are free to add any filter you want.

 with cte as ( 
      SELECT *, ROW_NUMBER() OVER (PARTITION BY loc_name, eventDate 
                                   ORDER BY Number_of_Teams DESC) as rn
      FROM tblEventDate
      WHERE eventDate >='09/01/2013'
 )
 SELECT loc_name, 
        eventDate, 
        COUNT(t_ID) as Number_of_Teams, 
        SUM(NbrOfPeople) as Number_of_People
 FROM cte
 WHERE rn <= 5
 GROUP BY loc_name, eventDate
 ORDER BY eventDate, loc_name

Sum of values from two rows of a table into a view

Try the below -

create view view_name as
    select tool, sum(stock)
    from tablename
    group by tool

T-SQL - Multiple WITH clause and then SUM total

If you want the total of y.Column1 when m.name matches any of y.m1, y.m2 and y.m3 then you need only 1 join and aggregation:

SELECT m.name, SUM(y.Column1) AS Total
FROM Mees m
INNER JOIN Listy y
ON m.name IN (y.m1, y.m2, y.m3)
WHERE Yr = 2020
GROUP BY m.name

If you want to add y.Column1 multiple times in case more than 1 of y.m1, y.m2 and y.m3 match m.name then use a CASE expression inside SUM():

SELECT m.name, 
       SUM(
         ( 
           CASE WHEN m.name = y.m1 THEN 1 ELSE 0 END +
           CASE WHEN m.name = y.m2 THEN 1 ELSE 0 END +
           CASE WHEN m.name = y.m3 THEN 1 ELSE 0 END
         ) * y.Column1
       ) AS Total
FROM Mees m
INNER JOIN Listy y
ON m.name IN (y.m1, y.m2, y.m3)
WHERE Yr = 2020
GROUP BY m.name

SQL query to sum multiple columns across multiple rows

All you seem to want is a sum(s) with a group by id

DROP TABLE IF EXISTS T;

CREATE TABLE T
(ID INT,    Value1 DECIMAL(10,2),   Value2 DECIMAL(10,2));
INSERT INTO T VALUES
(1, 1033.90 ,0.00),
(1, 0.00       ,1033.90),
(1, 1181.60 ,0.00);


SELECT ID , SUM(VALUE1) - SUM(VALUE2) AS TOT
FROM T
GROUP BY ID;

+------+---------+
| ID   | TOT     |
+------+---------+
|    1 | 1181.60 |
+------+---------+
1 row in set (0.001 sec)

and the group by is unnecessary if you have only 1 id. If you want a running total which where I think Lamu is coming from then you would need some way of ordering events

SQL SUM of a Column in WHERE Clause

;With cte(OrderNo,OrderQuantity)
AS
(
SELECT 100,250.00 Union all
SELECT 101,455.10 Union all
SELECT 102, 89.55 Union all
SELECT 103,112.15 Union all
SELECT 104,330.12 
)
SELECT OrderNo,OrderQuantity From
(
SELECT *,SUM(OrderQuantity) Over( Order by OrderNo) AS SumOrderQuantity from cte
)Dt
Where Dt.SumOrderQuantity <=800

OutPut

--------------------------
OrderNo     OrderQuantity
--------------------------
100             250.00
101             455.10
102              89.55

---

Related Topics