SQL Query to Sum the Data

How do you get the sum of values by day, but day in columns SQL

If you know what the days are, you can use conditional aggregation:

select client_name,
       sum(case when date = '2021-01-01' then order_value end) as date_20210101,
       sum(case when date = '2021-01-02' then order_value end) as date_20210102,
       sum(case when date = '2021-01-03' then order_value end) as date_20210103
from t
group by client_name ;

If you don't know the specific dates (i.e., you want them based on the data or a variable number), then you need to use dynamic SQL. That means that you construct the SQL statement as a string and then execute it.

Sql Query To Sum Amount Before the Date And After Between Dates

I would suggest putting the value in two separate columns. If you want the result by account:

SELECT Account,
       (SUM(CASE WHEN SaveDate < '2021-01-01' THEN DebitAmount ELSE 0 END) -
        SUM(CASE WHEN SaveDate < '2021-01-01' THEN CreditAmount ELSE 0 END)
      ) AS Amount_Before,
       (SUM(CASE WHEN SaveDate BETWEEN '2021-01-01' AND '2021-03-01' THEN DebitAmount ELSE 0 END) -
        SUM(CASE WHEN SaveDate BETWEEN '2021-01-01' AND '2021-03-01' THEN CreditAmount ELSE 0 END)
      ) AS Amount_After
FROM Ledger 
GROUP BY Account;

If you want totals, then remove Account from the SELECT and remove the GROUP BY.

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.

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