CODEWITHSUNDEEP
sql
Alul R
Alul R

Reputation: 51

How to SUM column if same value

I have query to SUM and COUNT my table, but i have trouble, i want to SUM column name after COUNT it. This is my table...

id  no_reg  name        date       qty
1   REG01   T-212-BS    2019-05-03  1
2   REG01   T-212-BS    2019-05-03  1
3   REG01   T-212-BS    2019-05-03  1
4   REG01   T-212-BA    2019-05-03  1
5   REG02   T-111-AA    2019-05-04  1
6   REG03   T-111-AB    2019-05-04  1

I create query....

SELECT     no_reg, COUNT(DISTINCT name) AS Name_qty, date, SUM(qty) AS qty
FROM         part
GROUP BY no_reg, name, date, qty

and result of query after execution...

no_reg   Name_qty   date        qty
REG01     1         2019-05-03  1
REG01     1         2019-05-03  3
REG02     1         2019-05-04  1
REG03     1         2019-05-04  1

But, I want results like this...

no_reg   Name_qty   date        qty
REG01     2         2019-05-03  4
REG02     1         2019-05-04  1
REG03     1         2019-05-04  1

Upvotes: 0

Views: 53

Answers (2)

Ben
Ben

Reputation: 1346

No need to group by name, even if you're using it in your distinct statement.

SELECT     no_reg, COUNT(DISTINCT name) AS Name_qty, date, SUM(qty) AS qty
FROM         part
GROUP BY no_reg, date

Upvotes: 2

Lawrence
Lawrence

Reputation: 347

You're grouping by qty, so any rows that do not have the same qty will be aggregated separately. Since qty is used in an aggregate function, you can remove it from the group by and it should give you the expected results

SELECT no_reg, COUNT(DISTINCT name) AS Name_qty, date, SUM(qty) AS qty
FROM part
GROUP BY no_reg, date

EDIT:

I also noticed that name was included in the group by. You can remove it too since it is used in the count aggregate

Upvotes: 1

Related Questions