none
why this query returns diferent number of records

    Question

  •  SELECT  sum(Znesek)as suma ,(isnull ( cast(Vrsta as nvarchar(50)),0) +' ---------> '+ isnull( cast( skupina as nvarchar(50)),0) ) as Polje2,vrsta , skupina, 2012 as leto
                         FROM RezultatPrihodki 
     group by Vrsta , Skupina   

    This query returns 50 rows

    while this two queries returns 51  rows

     SELECT  sum(Znesek)as suma ,(isnull ( cast(Vrsta as nvarchar(50)),0) +' ---------> '+ isnull( cast( skupina as nvarchar(50)),0) ) as Polje2,vrsta , skupina, 2012 as leto
                         FROM RezultatPrihodki  where Leto = 2011
     group by Vrsta , Skupina   
     
         
        
        SELECT  0 as suma ,(isnull ( cast(Vrsta as nvarchar(50)),0) +' ---------> '+ isnull( cast( skupina as nvarchar(50)),0) ) as Polje2,vrsta , skupina,2012 as leto
                         FROM RezultatPrihodki a  where Leto in (2012,2010) and not EXISTS
                          (
                         SELECT Vrsta , Skupina
                         FROM RezultatPrihodki b 
                         where Leto =2011
                         AND a.Vrsta = b.Vrsta
    	                 AND a.skupina = b.skupina
                           )
        group by a.Vrsta , a.Skupina  

    Why ? what I am doing wrong?

    Thanks

    Leposava Knez

    Monday, November 19, 2012 6:52 AM

Answers

  • >exists works strange with nulls ....

    Hi Leposava,

    It is not the EXISTS, it is the JOIN equal operator:

    AND a.Vrsta = b.Vrsta
    	                 AND a.skupina = b.skupina

    For NULL value you have to use the "is null" operator:

    a.Vrsta is NULL

    Take a look at the results below:

    DECLARE @int1 int = NULL, @int2 int = 77, @int3 int = 77, @int4 int = NULL;
    
    SELECT 1 WHERE @int2 = @int3; -- 1
    --(1 row(s) affected)
    
    SELECT 1 WHERE @int1 = @int3;
    -- (0 row(s) affected)
    
    SELECT 1 WHERE @int1 = @int4;
    -- (0 row(s) affected)
    
    SELECT 1 WHERE @int1 is NULL;  -- 1
    -- (1 row(s) affected)
    
    SELECT 1 WHERE @int1 = NULL;
    -- (0 row(s) affected)


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, November 19, 2012 3:29 PM
    Moderator
  • >Vote As HelpfulI am not sure why Kalman thinks everything is OK.

    Leposava has NULLs in the GROUP BY keys. That is giving the trouble.

    You cannot test like this when NULL values involved:

    where Leto =2011
                         AND a.Vrsta = b.Vrsta
    	                 AND a.skupina = b.skupina

    Likely that is the source of duplicate.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    Monday, November 19, 2012 10:33 AM
    Moderator
  • I updated the table and remove the null values now it works ..... exists works strange with nulls ....
    • Marked as answer by Squirrel30 Monday, November 19, 2012 11:34 AM
    Monday, November 19, 2012 11:33 AM

All replies

  • Hi Leposava,

    Can you post the different result line?

    My guess: you are running them against two different databases.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, November 19, 2012 7:06 AM
    Moderator
  • No, I am working on one database....
    Monday, November 19, 2012 7:12 AM
  • Can you post the different results? Thanks.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    suma	Polje2	vrsta	skupina	leto
    4533,162375	Protivirusni programi ---------> Programska oprema	Protivirusni programi	Programska oprema	2012
    508,8516	Protivirusni programi ---------> 0	Protivirusni programi	NULL	2012
    1802,4	Spletno gostovanje ---------> 0	Spletno gostovanje	NULL	2012
    822	Trash-strojna oprema ---------> 0	Trash-strojna oprema	NULL	2012
    8697,360793572	Programska oprema Microsoft ---------> Programska oprema	Programska oprema Microsoft	Programska oprema	2012
    2342,4	Trash-strojna oprema ---------> Programska oprema	Trash-strojna oprema	Programska oprema	2012
    3294,0387	 ---------> Programska oprema		Programska oprema	2012
    999234,991880026	Nadgradnje ---------> Programska oprema	Nadgradnje	Programska oprema	2012
    468	Spletno gostovanje ---------> Programska oprema	Spletno gostovanje	Programska oprema	2012
    435915,259459993	Nadgradnje ---------> 0	Nadgradnje	NULL	2012
    8371,313528	Čitalci črtnih kod ---------> POS Oprema	Čitalci črtnih kod	POS Oprema	2012
    86716,4087483601	POS sistemi ---------> POS Oprema	POS sistemi	POS Oprema	2012
    56014,6264831761	POS tiskalniki ---------> POS Oprema	POS tiskalniki	POS Oprema	2012
    2269,526	Potrošni material ---------> POS Oprema	Potrošni material	POS Oprema	2012
    231,6	Tiskalniki ---------> 	Tiskalniki		2012
    1647,29772	Računalniška oprema ---------> 	Računalniška oprema		2012
    457853,632514768	Storitve ---------> Programska oprema	Storitve	Programska oprema	2012
    304,296	Trash ---------> 	Trash		2012
    20,292	 ---------> 0		NULL	2012
    180	Programi Andersen ---------> 	Programi Andersen		2012
    245,27352	Programi Microsoft ---------> 	Programi Microsoft		2012
    326264,438902004	Storitve ---------> 0	Storitve	NULL	2012
    1632,582	 ---------> POS Oprema		POS Oprema	2012
    4299,07777672	POS predali ---------> POS Oprema	POS predali	POS Oprema	2012
    58,7328	Protivirusni programi ---------> 	Protivirusni programi		2012
    690	 ---------> HP Strežniki		HP Strežniki	2012
    534,78	HP Monitorji ---------> Programska oprema	HP Monitorji	Programska oprema	2012
    426,792	Čitalci črtne kode ---------> POS Oprema	Čitalci črtne kode	POS Oprema	2012
    8445,18400000001	Nadgradnje ---------> 	Nadgradnje		2012
    198,8544	Trash-strojna oprema ---------> 	Trash-strojna oprema		2012
    7884,0946	 ---------> 			2012
    314,058	Spletno gostovanje ---------> 	Spletno gostovanje		2012
    1137,6	Tehtnice ---------> Programska oprema	Tehtnice	Programska oprema	2012
    1425,2094	Prenosni terminali ---------> POS Oprema	Prenosni terminali	POS Oprema	2012
    4249,342	Trash ---------> Programska oprema	Trash	Programska oprema	2012
    1705,8	Računalniška oprema ---------> 0	Računalniška oprema	NULL	2012
    1446	HP Namizni računalniki ---------> Računalniška oprema	HP Namizni računalniki	Računalniška oprema	2012
    2236,584	Računalniška oprema ---------> Programska oprema	Računalniška oprema	Programska oprema	2012
    7,44	Trash ---------> 0	Trash	NULL	2012
    996,84	Programi Microsoft ---------> Programska oprema	Programi Microsoft	Programska oprema	2012
    12149,22	Programi Andersen ---------> 0	Programi Andersen	NULL	2012
    83816,7737247483	Storitve ---------> 	Storitve		2012
    214,6998	HP Laserski tiskalniki ---------> HP Tiskalniki	HP Laserski tiskalniki	HP Tiskalniki	2012
    0	Terminali ---------> POS Oprema	Terminali	POS Oprema	2012
    2966,552	0 ---------> Programska oprema	NULL	Programska oprema	2012
    687392,779791352	Programi Andersen ---------> Programska oprema	Programi Andersen	Programska oprema	2012
    1030,652	POS prikazovalniki ---------> POS Oprema	POS prikazovalniki	POS Oprema	2012
    171,6	HP Monitorji ---------> 	HP Monitorji		2012
    944,445701508	 ---------> Računalniška oprema		Računalniška oprema	2012
    2699,3288	0 ---------> 0	NULL	NULL	2012
    suma	Polje2	vrsta	skupina	leto
    0	 ---------> 0		NULL	2012
    0	 ---------> HP Strežniki		HP Strežniki	2012
    0	 ---------> Računalniška oprema		Računalniška oprema	2012
    0	0 ---------> 0	NULL	NULL	2012
    0	0 ---------> Programska oprema	NULL	Programska oprema	2012
    0	Čitalci črtne kode ---------> POS Oprema	Čitalci črtne kode	POS Oprema	2012
    0	HP Laserski tiskalniki ---------> HP Tiskalniki	HP Laserski tiskalniki	HP Tiskalniki	2012
    0	HP Monitorji ---------> 	HP Monitorji		2012
    0	HP Namizni računalniki ---------> Računalniška oprema	HP Namizni računalniki	Računalniška oprema	2012
    0	Nadgradnje ---------> 	Nadgradnje		2012
    0	Nadgradnje ---------> 0	Nadgradnje	NULL	2012
    0	Programi Andersen ---------> 	Programi Andersen		2012
    0	Programi Andersen ---------> 0	Programi Andersen	NULL	2012
    0	Programi Microsoft ---------> 	Programi Microsoft		2012
    0	Protivirusni programi ---------> 	Protivirusni programi		2012
    0	Protivirusni programi ---------> 0	Protivirusni programi	NULL	2012
    0	Računalniška oprema ---------> 	Računalniška oprema		2012
    0	Računalniška oprema ---------> 0	Računalniška oprema	NULL	2012
    0	Spletno gostovanje ---------> 	Spletno gostovanje		2012
    0	Spletno gostovanje ---------> 0	Spletno gostovanje	NULL	2012
    0	Storitve ---------> 	Storitve		2012
    0	Storitve ---------> 0	Storitve	NULL	2012
    0	Terminali ---------> POS Oprema	Terminali	POS Oprema	2012
    0	Tiskalniki ---------> 	Tiskalniki		2012
    0	Trash ---------> 	Trash		2012
    0	Trash ---------> 0	Trash	NULL	2012
    0	Trash-strojna oprema ---------> 	Trash-strojna oprema		2012
    0	Trash-strojna oprema ---------> 0	Trash-strojna oprema	NULL	2012
    99,36	 ---------> POS Oprema		POS Oprema	2012
    118,8	Trash ---------> Programska oprema	Trash	Programska oprema	2012
    121,152	POS prikazovalniki ---------> POS Oprema	POS prikazovalniki	POS Oprema	2012
    349,2	Tehtnice ---------> Programska oprema	Tehtnice	Programska oprema	2012
    468	Spletno gostovanje ---------> Programska oprema	Spletno gostovanje	Programska oprema	2012
    534,78	HP Monitorji ---------> Programska oprema	HP Monitorji	Programska oprema	2012
    779,28	Programi Microsoft ---------> Programska oprema	Programi Microsoft	Programska oprema	2012
    976,968	Potrošni material ---------> POS Oprema	Potrošni material	POS Oprema	2012
    1031,52	 ---------> 			2012
    1425,2094	Prenosni terminali ---------> POS Oprema	Prenosni terminali	POS Oprema	2012
    1431,6	POS predali ---------> POS Oprema	POS predali	POS Oprema	2012
    1573,183975	Protivirusni programi ---------> Programska oprema	Protivirusni programi	Programska oprema	2012
    2205,87	 ---------> Programska oprema		Programska oprema	2012
    2236,584	Računalniška oprema ---------> Programska oprema	Računalniška oprema	Programska oprema	2012
    2278,06	0 ---------> Programska oprema	NULL	Programska oprema	2012
    2342,4	Trash-strojna oprema ---------> Programska oprema	Trash-strojna oprema	Programska oprema	2012
    3793,1442	Čitalci črtnih kod ---------> POS Oprema	Čitalci črtnih kod	POS Oprema	2012
    5109,73056	Programska oprema Microsoft ---------> Programska oprema	Programska oprema Microsoft	Programska oprema	2012
    22916,153376	POS tiskalniki ---------> POS Oprema	POS tiskalniki	POS Oprema	2012
    26003,485856	POS sistemi ---------> POS Oprema	POS sistemi	POS Oprema	2012
    252804,002154761	Storitve ---------> Programska oprema	Storitve	Programska oprema	2012
    271916,645074164	Programi Andersen ---------> Programska oprema	Programi Andersen	Programska oprema	2012
    520758,572319997	Nadgradnje ---------> Programska oprema	Nadgradnje	Programska oprema	2012


    Monday, November 19, 2012 7:27 AM
  • Can you add

    ORDER by a.Vrsta , a.Skupina 

    to the queries and post again?  Thanks.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    suma	Polje2	vrsta	skupina	leto
    2699,3288	0 ---------> 0	NULL	NULL	2012
    2966,552	0 ---------> Programska oprema	NULL	Programska oprema	2012
    20,292	 ---------> 0		NULL	2012
    7884,0946	 ---------> 			2012
    690	 ---------> HP Strežniki		HP Strežniki	2012
    1632,582	 ---------> POS Oprema		POS Oprema	2012
    3294,0387	 ---------> Programska oprema		Programska oprema	2012
    944,445701508	 ---------> Računalniška oprema		Računalniška oprema	2012
    426,792	Čitalci črtne kode ---------> POS Oprema	Čitalci črtne kode	POS Oprema	2012
    8371,313528	Čitalci črtnih kod ---------> POS Oprema	Čitalci črtnih kod	POS Oprema	2012
    214,6998	HP Laserski tiskalniki ---------> HP Tiskalniki	HP Laserski tiskalniki	HP Tiskalniki	2012
    171,6	HP Monitorji ---------> 	HP Monitorji		2012
    534,78	HP Monitorji ---------> Programska oprema	HP Monitorji	Programska oprema	2012
    1446	HP Namizni računalniki ---------> Računalniška oprema	HP Namizni računalniki	Računalniška oprema	2012
    435915,259459993	Nadgradnje ---------> 0	Nadgradnje	NULL	2012
    8445,18400000001	Nadgradnje ---------> 	Nadgradnje		2012
    999234,991880026	Nadgradnje ---------> Programska oprema	Nadgradnje	Programska oprema	2012
    4299,07777672	POS predali ---------> POS Oprema	POS predali	POS Oprema	2012
    1030,652	POS prikazovalniki ---------> POS Oprema	POS prikazovalniki	POS Oprema	2012
    86716,4087483601	POS sistemi ---------> POS Oprema	POS sistemi	POS Oprema	2012
    56014,6264831761	POS tiskalniki ---------> POS Oprema	POS tiskalniki	POS Oprema	2012
    2269,526	Potrošni material ---------> POS Oprema	Potrošni material	POS Oprema	2012
    1425,2094	Prenosni terminali ---------> POS Oprema	Prenosni terminali	POS Oprema	2012
    12149,22	Programi Andersen ---------> 0	Programi Andersen	NULL	2012
    180	Programi Andersen ---------> 	Programi Andersen		2012
    687392,779791352	Programi Andersen ---------> Programska oprema	Programi Andersen	Programska oprema	2012
    245,27352	Programi Microsoft ---------> 	Programi Microsoft		2012
    996,84	Programi Microsoft ---------> Programska oprema	Programi Microsoft	Programska oprema	2012
    8697,360793572	Programska oprema Microsoft ---------> Programska oprema	Programska oprema Microsoft	Programska oprema	2012
    508,8516	Protivirusni programi ---------> 0	Protivirusni programi	NULL	2012
    58,7328	Protivirusni programi ---------> 	Protivirusni programi		2012
    4533,162375	Protivirusni programi ---------> Programska oprema	Protivirusni programi	Programska oprema	2012
    1705,8	Računalniška oprema ---------> 0	Računalniška oprema	NULL	2012
    1647,29772	Računalniška oprema ---------> 	Računalniška oprema		2012
    2236,584	Računalniška oprema ---------> Programska oprema	Računalniška oprema	Programska oprema	2012
    1802,4	Spletno gostovanje ---------> 0	Spletno gostovanje	NULL	2012
    314,058	Spletno gostovanje ---------> 	Spletno gostovanje		2012
    468	Spletno gostovanje ---------> Programska oprema	Spletno gostovanje	Programska oprema	2012
    326264,438902004	Storitve ---------> 0	Storitve	NULL	2012
    83816,7737247483	Storitve ---------> 	Storitve		2012
    457853,632514768	Storitve ---------> Programska oprema	Storitve	Programska oprema	2012
    1137,6	Tehtnice ---------> Programska oprema	Tehtnice	Programska oprema	2012
    0	Terminali ---------> POS Oprema	Terminali	POS Oprema	2012
    231,6	Tiskalniki ---------> 	Tiskalniki		2012
    7,44	Trash ---------> 0	Trash	NULL	2012
    304,296	Trash ---------> 	Trash		2012
    4249,342	Trash ---------> Programska oprema	Trash	Programska oprema	2012
    822	Trash-strojna oprema ---------> 0	Trash-strojna oprema	NULL	2012
    198,8544	Trash-strojna oprema ---------> 	Trash-strojna oprema		2012
    2342,4	Trash-strojna oprema ---------> Programska oprema	Trash-strojna oprema	Programska oprema	2012

    suma	Polje2	vrsta	skupina	leto
    2278,06	0 ---------> Programska oprema	NULL	Programska oprema	2012
    1031,52	 ---------> 			2012
    99,36	 ---------> POS Oprema		POS Oprema	2012
    2205,87	 ---------> Programska oprema		Programska oprema	2012
    3793,1442	Čitalci črtnih kod ---------> POS Oprema	Čitalci črtnih kod	POS Oprema	2012
    534,78	HP Monitorji ---------> Programska oprema	HP Monitorji	Programska oprema	2012
    520758,572319997	Nadgradnje ---------> Programska oprema	Nadgradnje	Programska oprema	2012
    1431,6	POS predali ---------> POS Oprema	POS predali	POS Oprema	2012
    121,152	POS prikazovalniki ---------> POS Oprema	POS prikazovalniki	POS Oprema	2012
    26003,485856	POS sistemi ---------> POS Oprema	POS sistemi	POS Oprema	2012
    22916,153376	POS tiskalniki ---------> POS Oprema	POS tiskalniki	POS Oprema	2012
    976,968	Potrošni material ---------> POS Oprema	Potrošni material	POS Oprema	2012
    1425,2094	Prenosni terminali ---------> POS Oprema	Prenosni terminali	POS Oprema	2012
    271916,645074164	Programi Andersen ---------> Programska oprema	Programi Andersen	Programska oprema	2012
    779,28	Programi Microsoft ---------> Programska oprema	Programi Microsoft	Programska oprema	2012
    5109,73056	Programska oprema Microsoft ---------> Programska oprema	Programska oprema Microsoft	Programska oprema	2012
    1573,183975	Protivirusni programi ---------> Programska oprema	Protivirusni programi	Programska oprema	2012
    2236,584	Računalniška oprema ---------> Programska oprema	Računalniška oprema	Programska oprema	2012
    468	Spletno gostovanje ---------> Programska oprema	Spletno gostovanje	Programska oprema	2012
    252804,002154761	Storitve ---------> Programska oprema	Storitve	Programska oprema	2012
    349,2	Tehtnice ---------> Programska oprema	Tehtnice	Programska oprema	2012
    0	Terminali ---------> POS Oprema	Terminali	POS Oprema	2012
    118,8	Trash ---------> Programska oprema	Trash	Programska oprema	2012
    2342,4	Trash-strojna oprema ---------> Programska oprema	Trash-strojna oprema	Programska oprema	2012

    0	0 ---------> 0	NULL	NULL	2012
    0	0 ---------> Programska oprema	NULL	Programska oprema	2012
    0	 ---------> 0		NULL	2012
    0	 ---------> HP Strežniki		HP Strežniki	2012
    0	 ---------> Računalniška oprema		Računalniška oprema	2012
    0	Čitalci črtne kode ---------> POS Oprema	Čitalci črtne kode	POS Oprema	2012
    0	HP Laserski tiskalniki ---------> HP Tiskalniki	HP Laserski tiskalniki	HP Tiskalniki	2012
    0	HP Monitorji ---------> 	HP Monitorji		2012
    0	HP Namizni računalniki ---------> Računalniška oprema	HP Namizni računalniki	Računalniška oprema	2012
    0	Nadgradnje ---------> 0	Nadgradnje	NULL	2012
    0	Nadgradnje ---------> 	Nadgradnje		2012
    0	Programi Andersen ---------> 0	Programi Andersen	NULL	2012
    0	Programi Andersen ---------> 	Programi Andersen		2012
    0	Programi Microsoft ---------> 	Programi Microsoft		2012
    0	Protivirusni programi ---------> 0	Protivirusni programi	NULL	2012
    0	Protivirusni programi ---------> 	Protivirusni programi		2012
    0	Računalniška oprema ---------> 0	Računalniška oprema	NULL	2012
    0	Računalniška oprema ---------> 	Računalniška oprema		2012
    0	Spletno gostovanje ---------> 0	Spletno gostovanje	NULL	2012
    0	Spletno gostovanje ---------> 	Spletno gostovanje		2012
    0	Storitve ---------> 0	Storitve	NULL	2012
    0	Storitve ---------> 	Storitve		2012
    0	Tiskalniki ---------> 	Tiskalniki		2012
    0	Trash ---------> 0	Trash	NULL	2012
    0	Trash ---------> 	Trash		2012
    0	Trash-strojna oprema ---------> 0	Trash-strojna oprema	NULL	2012
    0	Trash-strojna oprema ---------> 	Trash-strojna oprema		2012

    Monday, November 19, 2012 7:33 AM
  • How do you get lines like these?

    2699,3288 0 ---------> 0 NULL NULL 2012
    20,292 ---------> 0 NULL 2012
    7884,0946 ---------> 2012

    Where are vrsta , skupina?

    Can you just post the extra vrsta , skupina?


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    somtimes vrsta is null and skupina is null

    somtimes vrsta is '' and skupina is ''  (blank string)

    Monday, November 19, 2012 7:50 AM
  • I suspect the NULL lines make the difference.

    What is the extra line for vrsta , skupina?

    I suggest you use the ISNULL(vrsta, 'NO VRST') function. Similarly for skupina.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    it is the same :( 
    Monday, November 19, 2012 8:05 AM
  • Can you post the output of "SELECT @@version"?

    Is RezultatPrihodki a table or a view?

    Given that the first query is less restrictive that the other two, somehting seems to be amiss.

    You can use

    SELECT ...
    EXECPT
    SELECT ...

    To determine which rows that are extra/missing from the result sets. This may give you further insight.

    If RezultatPrihodki is a table, run DBCC CHECKTABLE on it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 8:16 AM
  • Can you post the  vrsta , skupina line which is different? Thanks.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    this is writes twice :

    suma Polje2 vrsta skupina leto
    0 0 ---------> Programska oprema NULL Programska oprema 2012
    2278,06 0 ---------> Programska oprema NULL Programska oprema 2012

    Monday, November 19, 2012 8:26 AM
  • That means you have that row in 2011 and also in in (2012,2010).

    If you do a UNION of the 2 queries, you will get the same count as in the single query.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    yes
    Monday, November 19, 2012 8:39 AM
  • Is that a data entry mistake?

    or Programska oprema NULL should be in both sets?

    So there is no mistery, if you do UNION for vrsta , skupina using the double queries, you get the same set as the single query.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, November 19, 2012 8:44 AM
    Moderator
  • Is that a data entry mistake?

    or Programska oprema NULL should be in both sets?

    So there is not mistery, if you do UNION for vrsta , skupina using the double queries, you get the same set as the single query.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    No no I get same 50 for the single query and 51 for the union ...
    Monday, November 19, 2012 8:46 AM
  • What do you get if you run the following query?

    ((SELECT vrsta, 
             skupina 
      FROM   rezultatprihodki 
      WHERE  leto = 2011 
      GROUP  BY vrsta, 
                skupina) 
     UNION 
     (SELECT vrsta, 
             skupina 
      FROM   rezultatprihodki a 
      WHERE  leto IN ( 2012, 2010 ) 
             AND NOT EXISTS (SELECT vrsta, 
                                    skupina 
                             FROM   rezultatprihodki b 
                             WHERE  leto = 2011 
                                    AND a.vrsta = b.vrsta 
                                    AND a.skupina = b.skupina) 
      GROUP  BY a.vrsta, 
                a.skupina)) 
    EXCEPT 
    SELECT vrsta, 
           skupina 
    FROM   rezultatprihodki 
    GROUP  BY vrsta, 
              skupina  


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Monday, November 19, 2012 8:53 AM
    Moderator
  • What do you get if you run the following query?

    ((SELECT vrsta, 
             skupina 
      FROM   rezultatprihodki 
      WHERE  leto = 2011 
      GROUP  BY vrsta, 
                skupina) 
     UNION 
     (SELECT vrsta, 
             skupina 
      FROM   rezultatprihodki a 
      WHERE  leto IN ( 2012, 2010 ) 
             AND NOT EXISTS (SELECT vrsta, 
                                    skupina 
                             FROM   rezultatprihodki b 
                             WHERE  leto = 2011 
                                    AND a.vrsta = b.vrsta 
                                    AND a.skupina = b.skupina) 
      GROUP  BY a.vrsta, 
                a.skupina)) 
    EXCEPT 
    SELECT vrsta, 
           skupina 
    FROM   rezultatprihodki 
    GROUP  BY vrsta, 
              skupina  


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    nothing empty
    Monday, November 19, 2012 8:58 AM
  • That means there is no problem! Computer Science wins again!

    You have to decide what to do with:

    0 0 ---------> Programska oprema NULL Programska oprema 2012
    2278,06 0 ---------> Programska oprema NULL Programska oprema 2012

    Merge them? Is this a data entry mistake?


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Monday, November 19, 2012 9:03 AM
    Moderator
  • That means there is no problem! Computer Science wins again!

    You have to decide what to do with:

    0 0 ---------> Programska oprema NULL Programska oprema 2012
    2278,06 0 ---------> Programska oprema NULL Programska oprema 2012

    Merge them? Is this a data entry mistake?


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    the first one 0 0 ---------> Programska oprema NULL Programska oprema 2012  is not good the second is ok why it puts in the set?

    how to marge them ?

    Monday, November 19, 2012 9:06 AM
  • maybe is sql server bug ?????
    Monday, November 19, 2012 9:10 AM
  • maybe is sql server bug ?????


    I posted a reply earlier in the thread, but it seems to have gone lost, so I repeat some of it:

    Can you post the output of "SELECT @@version"?

    Is RezultatPrihodki a table or a view?

    Given that the first query is less restrictive that the other two, somehting seems to be amiss. That is, a bug or corruption. If RezultatPrihodki is a table, run DBCC CHECKTABLE on it. If it is a view, it may be better to run DBCC CHECKDB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 9:13 AM
  • Why do you mark 2011 data as 2012?

     SELECT  sum(Znesek)as suma ,(isnull ( cast(Vrsta as nvarchar(50)),0) +' ---------> '+ isnull( cast( skupina as nvarchar(50)),0) ) as Polje2,
    vrsta , skupina, 2012 as leto
                         FROM RezultatPrihodki  where Leto = 2011
     group by Vrsta , Skupina 

    >0 0 ---------> Programska oprema NULL Programska oprema

    Should this only be in 2011? or (2010,2012)?

    Is this a data entry mistake? Did you locate the record which is causing the duplication?

    Did you talk to the data entry supervisor?

    Did you talk to the programmer who wrote the data entry software?

    We cannot give you help with data entry problems.

    The SQL works correctly.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, November 19, 2012 9:14 AM
    Moderator
  • maybe is sql server bug ?????


    I posted a reply earlier in the thread, but it seems to have gone lost, so I repeat some of it:

    Can you post the output of "SELECT @@version"?

    Is RezultatPrihodki a table or a view?

    Given that the first query is less restrictive that the other two, somehting seems to be amiss. That is, a bug or corruption. If RezultatPrihodki is a table, run DBCC CHECKTABLE on it. If it is a view, it may be better to run DBCC CHECKDB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) (Hypervisor)

    it is a table

    Monday, November 19, 2012 9:17 AM
  • DBCC results for 'RezultatPrihodki'.

    There are 22379 rows in 897 pages for object "RezultatPrihodki".

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Monday, November 19, 2012 9:20 AM
  • I am not sure why Kalman thinks everything is OK.

    We have

    SELECT a, b, SUM(c) FROM tbl GROUP BY a, b

    and this returns 50 rows. Then we have a query

    SELECT a, b, SUM(c) FROM tbl WHERE d = 'something' GROUP BY a, b

    and this returns more rows. A WHERE clause usually reduces the number of rows in a result set or at least keeps it the same.

    Leposava, you have the RTM version of SQL 2008 R2, and there is Service Pack 2 available. I know of no bug that can cause this behaviour, but installing SP2 is a start.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 10:28 AM
  • I am not sure why Kalman thinks everything is OK.

    We have

    SELECT a, b, SUM(c) FROM tbl GROUP BY a, b

    and this returns 50 rows. Then we have a query

    SELECT a, b, SUM(c) FROM tbl WHERE d = 'something' GROUP BY a, b

    and this returns more rows. A WHERE clause usually reduces the number of rows in a result set or at least keeps it the same.

    Leposava, you have the RTM version of SQL 2008 R2, and there is Service Pack 2 available. I know of no bug that can cause this behaviour, but installing SP2 is a start.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 10:29 AM
  • >Vote As HelpfulI am not sure why Kalman thinks everything is OK.

    Leposava has NULLs in the GROUP BY keys. That is giving the trouble.

    You cannot test like this when NULL values involved:

    where Leto =2011
                         AND a.Vrsta = b.Vrsta
    	                 AND a.skupina = b.skupina

    Likely that is the source of duplicate.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    Monday, November 19, 2012 10:33 AM
    Moderator
  • Leposava has NULLs in the GROUP BY keys. That is giving the trouble.

    When it comes to GROUP BY and DISTINCT, NULL counts as any other value.

    Yes, the NOT EXISTS subquery in her original post has a problem with NULL, but there is a WHERE clause and the query returns more rows than the query without a WHERE clause.

    Apparently these are the duplicates:

    suma            Polje2                                             vrsta skupina                     leto
    0             0 ---------> Programska oprema NULL    Programska oprema 2012
    2278,06 0 ---------> Programska oprema NULL    Programska oprema 2012

    They should be one row in the result set, not two.

    It seems that there is reason to run a query like:

    SELECT *, datalength(skupina)
    FROM   RezultatPrihodki
    WHERE  vrsta IS NULL
      AND  skupina LIKE 'Programska oprema%'

    and study the rows more closely, not the least the row with Znesek = 0. I would also recommend adding COUNT(*) to the queries, to see how many rows there is in each basket. That is

     SELECT  sum(Znesek)as suma , COUNT(*),
             (isnull ( cast(Vrsta as nvarchar(50)),0) +' ---------> '+
              isnull( cast( skupina as nvarchar(50)),0) ) as Polje2,
              vrsta, skupina, 2012 as leto
     FROM RezultatPrihodki 
     where Leto = 2011

    With and without the WHERE clause.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 11:27 AM
  • I updated the table and remove the null values now it works ..... exists works strange with nulls ....
    • Marked as answer by Squirrel30 Monday, November 19, 2012 11:34 AM
    Monday, November 19, 2012 11:33 AM
  • I am not sure why Kalman thinks everything is OK.

    We have

    SELECT a, b, SUM(c) FROM tbl GROUP BY a, b

    and this returns 50 rows. Then we have a query

    SELECT a, b, SUM(c) FROM tbl WHERE d = 'something' GROUP BY a, b

    and this returns more rows. A WHERE clause usually reduces the number of rows in a result set or at least keeps it the same.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    No, there are _two_ queries in the second case. And their WHERE clauses don't take possible NULLs into account which may  be a reason why some records are counted  in both result sets.


    Serg

    Monday, November 19, 2012 11:44 AM
  • No, there are two queries in the second case. And their WHERE clauses don't take possible NULLs into account which may  be a reason why some records are counted  in both result sets.

    Yes, I saw that there are two queries. But I took it as they returned 51 rows each. If the queries returned 51 rows in total, it is of course a completely different matter. (I will have to admit that I did not read the data dumps that Kalman asked for very closely.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 19, 2012 11:54 AM
  • NULLs are frequent source of problems and the queries agains NULLable data should be cooked very carefully.

    Serg

    Monday, November 19, 2012 12:10 PM
  • >exists works strange with nulls ....

    Hi Leposava,

    It is not the EXISTS, it is the JOIN equal operator:

    AND a.Vrsta = b.Vrsta
    	                 AND a.skupina = b.skupina

    For NULL value you have to use the "is null" operator:

    a.Vrsta is NULL

    Take a look at the results below:

    DECLARE @int1 int = NULL, @int2 int = 77, @int3 int = 77, @int4 int = NULL;
    
    SELECT 1 WHERE @int2 = @int3; -- 1
    --(1 row(s) affected)
    
    SELECT 1 WHERE @int1 = @int3;
    -- (0 row(s) affected)
    
    SELECT 1 WHERE @int1 = @int4;
    -- (0 row(s) affected)
    
    SELECT 1 WHERE @int1 is NULL;  -- 1
    -- (1 row(s) affected)
    
    SELECT 1 WHERE @int1 = NULL;
    -- (0 row(s) affected)


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, November 19, 2012 3:29 PM
    Moderator