CODEWITHSUNDEEP
sql
hakim homecent
hakim homecent

Reputation: 69

SQL Server How To Use Group if there is a column with a different value?

first take a look at my table

IDMutasi     ItemID ItemCode    ItemName    Lokasi  Seri    Quantity
1           10      kode1       nama1       A1      001     50
2           10      kode1       nama1       A1      002     100
3           10      kode1       nama1       A2      001     50
3           10      kode1       nama1       A2      003     150
3           10      kode1       nama1       A2      003     150
4           11      kode2       nama2       A1      011     50
5           11      kode2       nama2       A1      011     150
6           12      kode3       nama3       A3      012     200

lets just say the sql for above table is :

select * from tb_mutasi

i want to make table above become like this :

ItemID  ItemCode    ItemName    Lokasi  Seri    Quantity
10      kode1       nama1       A1      001     50
10      kode1       nama1       A1      002     100
10      kode1       nama1       A2      001     50
10      kode1       nama1       A2      003     300
11      kode2       nama2       A1      011     200
12      kode3       nama3       A3      012     200

the table above shown there is no duplicate if ItemCode, Lokasi and Seri is same then the Quantity will be increased. for the Quantity i can get it using SUM but whenever i using group by i always get error is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause no matter if i use group from Seri or Lokasi, i always get that result.

Any idea how to do that ?

Upvotes: 0

Views: 43

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

Here is a working group by query based on your description and sample data:

SELECT
    ItemID,
    ItemCode,
    ItemName,
    Lokasi,
    Seri,
    SUM(Quantity) AS Quantity
FROM tb_mutasi
GROUP BY
    ItemID,
    ItemCode,
    ItemName,
    Lokasi,
    Seri;

The requirement for GROUP BY in SQL Server is that every column which appears in the select clause also appears in the GROUP BY clause, or is an aggregate. Hence, we can select SUM(Quantity) because it is an aggregate, even though Quantity does not appear in GROUP BY.

Upvotes: 1

Related Questions