SQL Group by and Sum

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

SQL server GROUP BY, SUM and retrieve a column value

;WITH cte AS (
    SELECT
       id
       ,timestamp
       ,num
       ,subid
       ,country
       ,name
       ,SUM(pay) OVER (PARTITION BY num, subid, country, name) as pay
       ,ROW_NUMBER() OVER (PARTITION BY num, subid, country, name ORDER BY timestamp DESC) as RowNum
    FROM
       report
)

SELECT *
FROM
    cte
WHERE
    RowNum = 1

This is where windowed functions come in handy. Using SUM() OVER instead of a group by and then using ROW_NUMBER() OVER you can then pick the row you want and get the aggregated sum.

SQL Fiddle: http://sqlfiddle.com/#!18/6b0c6/5

group by query with sum all value

in this way also we can do

   Select 
T.productID,
T.Type,
T.Tamount,
TT.FAmount
     from (
select 
        productID, 
        Type, 
        SUM(Amount)Tamount 
from 
    Product
GROUP BY  productID, Type
)T
INNER JOIN (
select 
    productID,
    SUM(Amount)FAmount 
from 
    Product
GROUP BY  productID
)TT
ON T.productID = TT.productID

How to use GROUP BY with MAX or SUM depending on conditions?

You can use MAX() and SUM() window functions:

SELECT DISTINCT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN
           MAX(CASE WHEN Date <= '2021-07-01' THEN Value END) OVER (PARTITION BY Name)
         WHEN Name IN ('name3', 'name4') THEN
           SUM(Value) OVER (PARTITION BY Name)
       END Value    
FROM tablename

Or, conditional aggregation:

SELECT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN MAX(CASE WHEN Date <= '2021-07-01' THEN Value END)
         WHEN Name IN ('name3', 'name4') THEN SUM(Value)
       END Value    
FROM tablename
GROUP BY Name

See the demo.

TSQL - Group by and sum not grouped column

I've found solution, not the fastest but working.

 select st.ProductId, SUM(st.Price)
 from Prices as p1 
 cross apply 
     (
        select ProductId, ShopId, MAX(Date) as MaxDate
        from Prices
        group by ShopId, ProductId
     ) as p2
 where p2.MaxDate = p1.Dt 
    and p2.Shopid = p1.ShopId
    and p2.ProductId = p1.ProductId
 group by p1.ProductId 
 order by p1.ProductId

SUM Values by Sequence Number and Group By Flag

This is a classic gaps-and-islands problem.

However, in this case the start of each island is clearly delineated by a P (or a row that is not C). So we don't need LAG for that.

We just need to assign a grouping ID for each island, which we can do using a windowed conditional COUNT. Then we simply group by that ID.

SELECT
  pv.SalesOrder,
  SalesOrderLine = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.SalesOrderLine END),
  MStockCode = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MStockCode END),
  MPrice = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MPrice END),
  MBomFlag = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MBomFlag END)
FROM (
    SELECT *,
      GroupingId = COUNT(NULLIF(t.MBomFlag, 'C')) OVER (PARTITION BY t.SalesOrder ORDER BY t.SalesOrderLine ROWS UNBOUNDED PRECEDING)
    FROM @tbl t
) pv
GROUP BY
  pv.SalesOrder,
  pv.GroupingId;

Note that NULLIF(t.MBomFlag, 'C') returns null if the flag is C, so COUNT will only count the other rows. You could also write that explicitly using COUNT(CASE WHEN t.MBomFlag = 'P' THEN 1 END)

db<>fiddle

SQL How to use SUM and Group By with multiple tables?

Here is a solution based on your data. Issue with your query is that you were joining tables on a non-unique column resulting in Cartesian product.

Data

DROP TABLE IF EXISTS A;

CREATE TABLE A
(num int,
 id int,
 col1 int);

 INSERT INTO A VALUES (1,     100,     0);
 INSERT INTO A VALUES (2,     100,     1);
 INSERT INTO A VALUES (3,     100,     0);
 INSERT INTO A VALUES (1,     101,     1);
 INSERT INTO A VALUES (2,     101,     1);
 INSERT INTO A VALUES (3 ,    101,     0);

DROP TABLE IF EXISTS B;

 CREATE TABLE B
(idx int,
 id int,
 col2 int);

 INSERT INTO B VALUES (1,     100,     20);
 INSERT INTO B VALUES (2,     100,     20);
 INSERT INTO B VALUES (3,     100,     20);
 INSERT INTO B VALUES (4,     101,     100);
 INSERT INTO B VALUES (5,     101,     100);

DROP TABLE IF EXISTS C;

CREATE TABLE C
(idx int,
 id int,
 col3 int);

 INSERT INTO C VALUES (1,     100,     1);
 INSERT INTO C VALUES (2,     100,     1);
 INSERT INTO C VALUES (3,     100,     1);
 INSERT INTO C VALUES (4,     101,     10);
 INSERT INTO C VALUES (5,     101,     1);

Solution

SELECT a_sum.id, col1_sum, col2_sum, col3_sum
 FROM (SELECT id, SUM(col1) AS col1_sum
         FROM a
        GROUP BY id ) a_sum
      JOIN 
      (SELECT id, SUM(col2) AS col2_sum
         FROM b
        GROUP BY id ) b_sum 
      ON (a_sum.id = b_sum.id)
      JOIN 
      (SELECT id, SUM(col3) AS col3_sum
         FROM c
        GROUP BY id ) c_sum 
      ON (a_sum.id = c_sum.id);

Result is as expected

Note: Do outer joins if an id doesnt have to be present in all three tables.

---

Related Topics