SQL- If the rows add 1 in a group add the total

Question

SQL- If the rows add 1 in a group add the total

Navigation

#1 by (1 votes)

0

Good morning friends,

I hope you can help me, I'm trying to make a query that depends on the category and if the sum of one column results in 1 the total is added.

Any contribution can help me:)

example

|---------------------|------------------|------------------|------------------|
|      categoria      |    categoria 2   |       valor      |       Total      |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.2       |        20        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.2       |        42        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.2       |        46        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.2       |        23        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.2       |         6        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.7       |        30        |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |        0.3       |        30        |
|---------------------|------------------|------------------|------------------|
|          b          |         c        |        0.6       |        80        |
|---------------------|------------------|------------------|------------------|
|          c          |         b        |        0.4       |        54        |
|---------------------|------------------|------------------|------------------|
|          c          |         b        |        0.6       |        23        |
|---------------------|------------------|------------------|------------------|

How I want it to come out

|---------------------|------------------|------------------|------------------|
|      categoria      |    categoria 2   |       valor      |       Total      |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |         1        |        137       |
|---------------------|------------------|------------------|------------------|
|          a          |         a        |         1        |        60        |
|---------------------|------------------|------------------|------------------|
|          c          |         b        |         1        |        77        |
|---------------------|------------------|------------------|------------------|

My main problem is that I want all the possible lines of the categories with a maximum value of 1 to go out

Those who do not complete 1 in the value does not matter if they appear.

Thank you very much:)

sql group-by

asked by Macarthur 08.02.2018 в 22:50

source

1 answer

Pass data from one component to another angular 4 How to redirect to another server in PHP

score 1 · Answer 1

You can add using the clause HAVING

CREATE TABLE dbo.categoria
(
  categoria  CHAR(1),
  categoria2 CHAR(1),
  valor      MONEY,
  total      MONEY
)

INSERT INTO categoria VALUES ('a', 'a', 0.2, 20)
INSERT INTO categoria VALUES ('a', 'a', 0.2, 42)
INSERT INTO categoria VALUES ('a', 'a', 0.2, 46)
INSERT INTO categoria VALUES ('a', 'a', 0.2, 23)
INSERT INTO categoria VALUES ('a', 'a', 0.2, 6)
INSERT INTO categoria VALUES ('b', 'c', 0.6, 80)
INSERT INTO categoria VALUES ('c', 'c', 0.4, 54)
INSERT INTO categoria VALUES ('c', 'c', 0.6, 23)

SELECT
  c.categoria,
  c.categoria2,
  sum(c.valor),
  sum(c.total)
FROM dbo.categoria c
GROUP BY c.categoria, categoria2
HAVING sum(c.valor) = 1