lundi 8 juin 2015

SQL Several Count

First sorry if my english is bad, it's not my native language.

I have two tables (A and B) with the following columns:

A: PRENUMERO (ID), DATA, ARMAZEM, TIPO

and

B: Autoreg (ID), PRENUMERO, PRODUTO

I want a result like:

*CountA, CountB, CountC, DATE *

CountA is when PRODUTO is equal to 1.

CountB is when PRODUTO is > than 1.

CountC is when PRODUTO has both 1 and > 1 in the same PRENUMERO.

I joined the tables like this:

SELECT DISTINCT
b.[Autoreg],
a.[PRENUMERO],
b.[PRENUMERO],
b.[PRODUTO]
FROM
b.[VendasPOS_Linhas]
JOIN a
ON b.[PRENUMERO]=a.[PRENUMERO]
WHERE
a.[DATA] > '2015-06-01 00:00:00.000' AND
a.[Armazem] = '111'

And this is the result:

enter image description here

So if I do [PRENUMERO] = '1' (for CountA) the result should be for example 1000 and if I do [PRENUMERO] > '1' (for CountB) the result should be for example 1100 and the CountC should be the rest (667). But I do not know how to do it.

As you can see in the line 11 and 12, they have the same PRENUMERO, but the PRODUTO is 1 and the other is 10900... so this is for CountC. If the PRENUMERO is the same (several exactly PRENUMERO) but the PRODUTO is 1 (CountA (like line 8 and 9)) or >1 (CountB) this count only as 1 (DICTINCT here?)

(IF PRODUTO = 1 THEN CountA; IF PRODUTO >1 THEN CountB)

Aucun commentaire:

Enregistrer un commentaire