Using Sum in an SQL Query

SQL Server: Error when trying to use SUM in SELECT Statement

You can use window functions:

SELECT rs.*, SUM(rs.amount) OVER () AS total_amount 
FROM repsales rs
WHERE rs.saledate BETWEEN '2005-08-01' AND '2005-08-31';

My answer is using SUM as an analytic/window function. This means that each row will be assigned a value, as contrasted to using SUM with GROUP BY, where an aggregation of rows takes place.

Using SUM(amount) OVER () defines the window to be the entire table. That is, the sum will be the total sum of the amount.

SQL Query with SUM with Group By

The GROUP BY clause forms rows for each unique combinaton of the columns you nominate in that clause.

If you want to show a sum for each month, then only include s.month in the group by clause: e.g

SELECT s.month, SUM(rs.total_reseller_sales)
FROM sales_report_resellers rs 
INNER JOIN resellers r ON rs.resellerid = r.resellerid 
INNER JOIN sales_report s ON rs.sid = s.sid 
WHERE (rs.sid > '294' AND rs.sid < '306') AND r.insidesales = 0 
  AND r.resellerid IN (7, 18, 22) 
GROUP BY s.month

If you include reseller details in the select clause also include them in the group by clause then there will be one row per reseller AND month

Need an efficient query for sum and sum of sums

You may try using SUM as a window function, to replace the correlated subquery:

SELECT 
    D1.Manager_name,
    D1.location_name,
    SUM(D1.sale_amount) sale_amount,
    SUM(SUM(D1.sale_amount)) OVER (PARTITION BY D1.Manager_name) total_amount
FROM details D1
GROUP BY 
    D1.Manager_name,
    D1.location_name;

Here is an explanation of what is happening. Window functions always are evaluated last. The only thing which executes after a window function is the ORDER BY clause. In the above case, after GROUP BY evaluates, the only columns available in the intermediate result are Manager_name, location_name, and SUM(sale_amount). When we use SUM as a window function, with a partition by manager, we therefore can find the total sum for each manager, across all aggregated locations.

How to use sum function in where clause in SQL Server?

You can just use HAVING:

select  top 1000 o.orderamount, sum(oi.amount), oi.orderid 
from orders o 
inner join orderitem oi on o.orderid = oi.orderid 
group by oi.orderid ,orderamount
HAVING orderamount = sum(oi.amount)
order by oi.orderid desc

Using SUM in an SQL query

Sum and count can be used to get the result you want:

select CustomerID, count(*) as Orders, ShipName, sum(Total) as Total
from Table
group by CustomerID, ShipName
order by count(*) desc;

SQL query using Sum() and count() functions

Consider using the filter syntax to aggreagate functions, which is standard SQL and that Postgres supports:

select 
    count(*) filter(where "exerciseType" = 'ROOT'   ) as total_root_exercises,
    count(*) filter(where "exerciseType" = 'DYNAMIC') as total_Dynamic_exercises,
    count(*) filter(where "exerciseType" = 'TEST'   ) as total_test_exercises 
FROM exer

If you were to write this without the filter syntax (as in your first attempt), a portable syntax is:

select 
    sum(case when "exerciseType" = 'ROOT'    then 1 else 0 end) as total_root_exercises,
    sum(case when "exerciseType" = 'DYNAMIC' then 1 else 0 end) as total_Dynamic_exercises,
    sum(case when "exerciseType" = 'TEST'    then 1 else 0 end) as total_test_exercises 
FROM exer

SQL SUM and Count in a single query

Both queries are almost same with same column used in GROUP BY clause, so you can combine them in single query having both aggregate functions COUNT() and SUM()

SELECT
    sPortfolioName
    ,Count(lot.lLotID) as TotalLots
    ,sum (tblExpense.mRate) as TotalExpense
FROM [Owners]

inner join Portfolio on Portfolio.lUserID = Owners.lUserID
inner Join tblExpense on tblExpense.lOwnersID = Owners.lOwnersID
inner Join Lot on Lot.lOwnersID = Owners.lOwnersID

where bManaged = 'Y' 
AND tblExpense.lExpenseCodeID = '5' 
Group By sPortfolioName 
Order By sPortfolioName

EDIT

Try this and check if its returning correct desired result

SELECT t1.sPortfolioName, t1.TotalLots, t2.TotalExpense
FROM
(
SELECT
    sPortfolioName
    ,Count(lot.lLotID) as TotalLots

FROM [Owners]

inner join Portfolio on Portfolio.lUserID = Owners.lUserID
inner Join tblExpense on tblExpense.lOwnersID = Owners.lOwnersID
inner Join Lot on Lot.lOwnersID = Owners.lOwnersID

where bManaged = 'Y' 
AND tblExpense.lExpenseCodeID = '5' 
Group By sPortfolioName
) As t1

INNER JOIN

(
  SELECT 
      Portfolio.sPortfolioName
      ,sum (tblExpense.mRate) as TotalExpense
  FROM [Owners]
  inner join Portfolio on Portfolio.lUserID = Owners.lUserID
  inner Join tblExpense on tblExpense.lOwnersID = Owners.lOwnersID
  where bManaged = 'Y' 
  AND tblExpense.lExpenseCodeID = '5' 
  Group By sPortfolioName
) As t2

ON t1.sPortfolioName = t2.sPortfolioName
ORDER BY t1.sPortfolioName

Select Query for Sum and group it

Solution in Oracle/DB2/Snowflake SQL:

(The LISTAGG function is ANSI/SQL:2016 compliant, but not generally supported in every recent RDBMS version)

SELECT
 LISTAGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item, 
 Supplier, 
 SUM(Qty) AS Qty, 
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable 
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2014 :

SELECT
 STUFF((select '|'+ t2.Item
        from yourtable t2
        where t2.Supplier = t.Supplier
        order by t2.Qty
        for xml path ('')),1,1,'') AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2017+ using STRING_AGG :

SELECT
 STRING_AGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier

---

Related Topics