SQL Contar ROWS

Asked

Viewed 222 times

3

Good!

I have two tables:

A: PRENUMERE (ID), DATE, STORAGE, TYPE

B: Autoreg (ID), PRENUMERE, PRODUCT

And I wanted to have a result like:

Totalcombustible, Totalloja, Misto

Where the Fuel is when the product is = 1, the Store is >1 and the mixed the two

If I see all Rows from the table I have give this: inserir a descrição da imagem aqui

Now I made the call like this:

SELECT
    (SELECT COUNT(DISTINCT [VendasPOS_Linhas].[PRENUMERO])
        FROM
            [VendasPOS_Linhas]
        JOIN    [VendasPOS_Cabecalhos]
        ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
        WHERE
            [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
            [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
            [VendasPOS_Cabecalhos].[Armazem] = '454' AND
            [VendasPOS_Linhas].[PRODUTO] > 1) AS QtdLoja,
    (SELECT COUNT(DISTINCT [VendasPOS_Linhas].[PRENUMERO])
        FROM
            [VendasPOS_Linhas]
        JOIN    [VendasPOS_Cabecalhos]
        ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
        WHERE
            [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
            [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
            [VendasPOS_Cabecalhos].[Armazem] = '454' AND
            [VendasPOS_Linhas].[PRODUTO] = 1) AS QtdCombustivel,
    (SELECT COUNT(DISTINCT [VendasPOS_Cabecalhos].[PRENUMERO]) from VendasPOS_Linhas JOIN    [VendasPOS_Cabecalhos]
        ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO] where DATA> '2015-06-01 00:00:00.000' and Armazem = '454' AND [VendasPOS_Cabecalhos].[FACT_VD] like 'T') AS Total

And the result is this:

inserir a descrição da imagem aqui

But right here I see some mistakes.

Total looks like it’s too much.

2º The totalComb and Totalloja are counting those that are mixed as well

3º In line 11 and 12 we can see that it has PRENUMERES equal but the product is both >1 and in the bottom line = 1 and this is part of the mixed. How do I see if a PRENUMERO has either 1 or >1 and it gives different lines. I mean, I have to see which are the duplicates and see if the same PRENUMERE has = 1 and >1 but not if it is possible.

RESOLVED:

SELECT 
    (SELECT COUNT(DISTINCT [VendasPOS_Linhas].[PRENUMERO]) FROM [VendasPOS_Linhas] 
     INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 1
        ) combustivel ON combustivel.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
        WHERE [VendasPOS_Linhas].[PRODUTO] = 1
     ) AS QtdeCombustivel,
    (SELECT COUNT(DISTINCT [VendasPOS_Linhas].[PRENUMERO]) FROM [VendasPOS_Linhas] 
     INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 1
        ) loja ON loja.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
        WHERE [VendasPOS_Linhas].[PRODUTO] > 1
     ) AS QtdeLoja,
    (SELECT COUNT(DISTINCT [VendasPOS_Linhas].[PRENUMERO]) FROM [VendasPOS_Linhas] 
      INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) > 1
        ) mista ON mista.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
     ) AS QtdeMista
sql

  • Your total may be returning more because you didn’t do the Join to make sure that will only count those who have relationship. As for the rest is a little confused for me :(

  • 1

    Yes that was missing, I fixed the total and it appears correctly now. For the rest, here is an image to see if it helps: ! http://i.imgur.com/toYKknJ.png

  • What is DBMS? SQL Server? Mysql?

  • @Bruno It is Sqlserver

2 answers

1


I tried to do a trick with the HAVING COUNT, but I don’t know if it will.

SELECT 
    (SELECT COUNT([VendasPOS_Linhas].[PRENUMERO]) FROM [MXPETROL_AB].[dbo].[VendasPOS_Linhas] 
     INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [MXPETROL_AB].[dbo].[VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 1
        ) combustivel ON combustivel.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
        WHERE [VendasPOS_Linhas].[PRODUTO] = 1
     ) AS QtdeCombustivel,
    (SELECT COUNT([VendasPOS_Linhas].[PRENUMERO]) FROM [MXPETROL_AB].[dbo].[VendasPOS_Linhas] 
     INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [MXPETROL_AB].[dbo].[VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 1
        ) loja ON loja.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
        WHERE [VendasPOS_Linhas].[PRODUTO] > 1
     ) AS QtdeLoja,
     (SELECT COUNT([VendasPOS_Linhas].[PRENUMERO]) FROM [MXPETROL_AB].[dbo].[VendasPOS_Linhas] 
      INNER JOIN
        (
            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [MXPETROL_AB].[dbo].[VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING MIN([VendasPOS_Linhas].[PRODUTO]) = 1 AND MAX([VendasPOS_Linhas].[PRODUTO]) <> 1 AND COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) > 
        ) mista ON mista.PRENUMERO = [VendasPOS_Linhas].[PRENUMERO]
     ) AS QtdeMista

Explanation

SELECT internal

            SELECT [VendasPOS_Linhas].[PRENUMERO]
            FROM
                [MXPETROL_AB].[dbo].[VendasPOS_Linhas]
            JOIN    [VendasPOS_Cabecalhos]
            ON    [VendasPOS_Linhas].[PRENUMERO] = [VendasPOS_Cabecalhos].[PRENUMERO]
            WHERE
                [VendasPOS_Cabecalhos].[FACT_VD] like 'T' AND
                [VendasPOS_Cabecalhos].[DATA] > '2015-06-01 00:00:00.000' AND 
                [VendasPOS_Cabecalhos].[Armazem] = '454'
            GROUP BY [VendasPOS_Linhas].[PRENUMERO]
            HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 1

Because of HAVING COUNT(...) = 1 will only return PRENUMERO that have 1 single PRODUTO whereas in the last SELECT i do > 1 to catch the PRENUMERO mixed

After the return of SELECT more from within, I make a COUNT(...) to know how many PRENUMERO distinguished returned me.

To find out if it’s fuel or shop I do at the time of COUNT mentioned above, if it is fuel is WHERE [VendasPOS_Linhas].[PRODUTO] = 1 while shop stays WHERE [VendasPOS_Linhas].[PRODUTO] > 1. In the case of the mixed is indifferent.

  • Thanks, I tried but I couldn’t. Appeared Qtddefuel to 0, Qtddeshop to 3387 and Qtdmista to 0

  • @Enato I put the wrong COUNT, I was passing PRENUMERO instead of PRODUTO, tries again :)

  • This appeared: http://i.imgur.com/xqZnbs7.png ?

  • @Enato as you are calculating the total?

  • thus: (SELECT COUNT(DISTINCT [Vendaspos_cabecalhos].[PRENUMERO]) from Vendaspos_linhas JOIN [Vendaspos_cabecalhos] ON [Vendaspos_linhas]. [PRENUMERO] = [Vendaspos_cabecalhos]. [PRENUMERO] Where DATA> '2015-06-01 00:00:00.000' and Armazem = '454' AND [Vendaspos_cabecalhos]. [FACT_VD] like’T') AS Total

  • Trade mine COUNT(1) for COUNT( DISTINCT [VendaPOS_Linhas]. [PRENUMERO])

  • It was nearby. The Qtdcomb was 1048. the store was at 1154 and the mixed at 380 which gives a total of 2582. It remained 75 :s

  • We are almost

  • @Enato Take a test: SELECT [Vendaspos_lines]. [PRENUMERO], COUNT(DISTINCT [Vendaspos_lines].[PRODUCT]) FROM [MXPETROL_AB]. [dbo]. [Vendaspos_linhas] JOIN [Vendaspos_cabecalhos] ON [Vendaspos_linhas]. [PRENUMERO] = [Vendaspos_cabecalhos]. [PRENUMERO] WHERE [Vendaspos_cabecalhos]. [FACT_VD] like’T' AND [Vendaspos_cabecalhos]. [DATA] > '2015-06-01 00:00:00.000' AND [Vendaspos_cabecalhos]. [Armazem] = '454' GROUP BY [Vendaspos_linhas]. [Prenumero] ORDER BY COUNT(DISTINCT [Vendaspos_lines].[PRODUCT]) DESC

  • Check if you have a prenumere with + 2 in the Count

  • Yes, 75 Rows. goes from +3 to +5

  • Is it normal? My SQL error then changes the last HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) = 2 for HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) > 1

  • I updated the answer with our changes here

  • It worked :D But the edited version does not work. I will edit mine with the right end result to be able to edit as well. Could I just give an explanation of how you hit it just to see if I get a better handle on it too?

  • @Enato I updated in the answer the explanation, if it got confused I can try to explain again. If you solved your problem, please mark the answer as the solution.

  • Thank you very much! I got it right

  • 1

    @Enato I changed in mine also the suggestion that I told you below, change the parts that you said did not work

Show 12 more comments

1

The question has already been answered but, still, I leave a more concise answer that avoids the many sub-queries of the original 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 TotalAbsoluto
FROM
(
    SELECT   VL.PRENUMERO
            ,COUNT(DISTINCT VL.PRODUTO) AS NumProduto
            ,MIN(VL.PRODUTO)            AS MinProduto
            ,MAX(VL.PRODUTO)            AS MaxProduto
    FROM    MXPETROL_AB.dbo.VendasPOS_Linhas VL
    INNER JOIN VendasPOS_Cabecalhos VC
       ON    VL.PRENUMERO = VC.PRENUMERO
    WHERE    VC.FACT_VD = 'T' 
      AND    VC.DATA > '2015-06-01 00:00:00.000' 
      AND    VC.Armazem = '454' 
    GROUP BY VL.PRENUMERO
) Res

I merely altered one of your conditions of 

VC.FACT_VD LIKE 'T'

for 

VC.FACT_VD = 'T'

since you were not using wildcards, the use of LIKE.

Edit:

As requested in the comments, this query will return the totals per day:

SELECT    DATA
         ,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 TotalAbsoluto
FROM
(
    SELECT   VC.DATA 
            ,VL.PRENUMERO
            ,COUNT(DISTINCT VL.PRODUTO) AS NumProduto
            ,MIN(VL.PRODUTO)            AS MinProduto
            ,MAX(VL.PRODUTO)            AS MaxProduto
    FROM    MXPETROL_AB.dbo.VendasPOS_Linhas VL
    INNER JOIN VendasPOS_Cabecalhos VC
       ON    VL.PRENUMERO = VC.PRENUMERO
    WHERE    VC.FACT_VD = 'T' 
      AND    VC.DATA > '2015-06-01 00:00:00.000' 
      AND    VC.Armazem = '454' 
    GROUP BY VC.DATA, VL.PRENUMERO
) Res
GROUP BY DATA
ORDER BY 1
  • 1

    Interesting the way you reduced it, I think it works. @Enato tested?

  • It worked yes! It got smaller so too, besides reducing it is not so heavy or is equal to the other code?

  • 1

    Only one thing, both in this code and in what I marked as a solution it is counting these as mixed is not? http://i.imgur.com/8CBFZvf.png In that test I did on top @Maicon-Carraro requested and gave some with +5, I went to see the prenumero of this and some have only of store or only of fuel

  • 1

    @Enato Try to change the HAVING COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) > 1 in the SELECT from the MY SELECT Player by HAVING MIN([VendasPOS_Linhas].[PRODUTO]) = 1 AND MAX([VendasPOS_Linhas].[PRODUTO]) <> 1 AND COUNT(DISTINCT [VendasPOS_Linhas].[PRODUTO]) > 1

  • It gave much less results for the Mixed (went from 500 to 169) but so does not give the right end result, it must be missing to add those that took the mixed to the Lodge. I think the problem is only the store because as the fuel is always 1 it counts well. The store product as it is >1 but there are several different numbers it should not count well in the store. Is there anything you can do in the countLoja? Anyway I’ll do the math manually to see if the results match well

  • 1

    @Enato, I changed the answer according to your comments. You can check if it produces the expected result?

  • Thank you so much! It’s perfect now. the <> means different?

  • 1

    Yes, it means different. I’m glad it worked.

  • Now I don’t know if I should get this one right or the other, because they both work. Just one more thing, if I want to have one more column next to the dates for each day is possible ?

  • 1

    You want instead of just showing a row with the total, a row is shown for each date you have in the table?

  • 1

    EDIT: I got it. I did it like this: CONVERT(varchar,Data,101) AS Data

Show 6 more comments

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.