SQL Several Count

Question

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:

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)

EDIT:

So I did this:

SELECT
    (SELECT
    COUNT(DISTINCT [B].[PRENUMERO])
FROM
    [B]
JOIN    [A]
ON    [B].[PRENUMERO] = [A].[PRENUMERO]
WHERE
    [A].[DATA] > '2015-06-01 00:00:00.000' AND
    [A].[Armazem] = '454' AND
    [B].[PRODUTO] > '1') AS X,
    (SELECT
    COUNT(DISTINCT [B].[PRENUMERO])
FROM
   [B]
JOIN    [A]
ON    [B].[PRENUMERO] = [A].[PRENUMERO]
WHERE
    [A].[DATA] > '2015-06-01 00:00:00.000' AND
    [A].[Armazem] = '454' AND
    [B].[PRODUTO] = '1') AS Y,
    (SELECT COUNT([A].[PRENUMERO]) from A where DATA> '2015-06-01 00:00:00.000' and Armazem = '454') AS Total

And this is the result:

But I think this is wrong because it's counting the CountC as well.

For example: If the same PRENUMERO as 1 and >1 it belongs to CountC. What can I do to not count does? (How do I check if PRENUMERO doesn't have 1 and >1 (diferent rows))

Answer 1

Solution

> SELECT    COUNT(DISTINCT CASE WHEN MinProduto = 1 AND MaxProduto = 1
> THEN PRENUMERO END) AS QtdCombustivel
>          ,COUNT(DISTINCT CASE WHEN MinProduto <> 1 AND MaxProduto <> 1 THEN PRENUMERO END) AS QtdLoja
>          ,COUNT(DISTINCT CASE WHEN MinProduto = 1 and MaxProduto <> 1 THEN PRENUMERO END) AS QtdMisto
>          ,COUNT(DISTINCT PRENUMERO) AS Total FROM (
>     SELECT  [VendasPOS_Linhas].[PRENUMERO]
>             ,MIN([VendasPOS_Linhas].[PRODUTO])            AS MinProduto
>             ,MAX([VendasPOS_Linhas].[PRODUTO])            AS MaxProduto
>     FROM    [VendasPOS_Linhas]
>     INNER JOIN [VendasPOS_Cabecalhos]
>        ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
>     WHERE    UPPER([VendasPOS_Cabecalhos].[FACT_VD]) IN ('T','F','C') 
>       AND    [VendasPOS_Cabecalhos].[DATA] > '2015-06-11 00:00:00.000' 
>       AND    [VendasPOS_Cabecalhos].[Armazem] = '404' 
>     GROUP BY [VendasPOS_Linhas].[PRENUMERO] , [VendasPOS_Cabecalhos].[DATA] )Res

Answer 2

Well If I understand you are trying to do the next.

• COUNT A -> Only products=1
• COUNT B -> Only products>1

• COUNT A -> products=1 AND Prenumero>1

  SELECT SUM(if(producto)=1,1,0) AS COUNT_A ,SUM(if(producto)>1,1,0) AS COUNT_B ,SUM((if(producto)=1 AND PRENUMERO>1),1,0) AS COUNT_C ,a.DATA FROM b JOIN a USING(PRENUMERO) WHERE a.DATA > '2015-06-01 00:00:00.000' AND a.Armazem = '111' AND b.producto>0 GROUP BY a.DATA;

You are sharing information between count A and count C, for discating this you could use the alias from each tabla to validate by example

a.prenumero>1 AND b.prenumero =1

but if this is your inner join field always will have the same value for both tables

Regards.

Answer 3

MySQL is pretty handy for this, since it's very forgiving of type conversions:

SELECT SUM(PRODUTO = 1) AS countA,
    SUM(PRODUTO > 1) AS countB,
    SUM((PRODUTO = 1) AND (PRENUMERO > 1)) AS countC
...

MySQL will take the boolean true/false of the = and > tests, convert them to integer 0 and 1, and them sum up those integers, giving you your counts.