# 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

• >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)```

Monday, November 19, 2012 3:29 PM
• >I 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.

Monday, November 19, 2012 10:33 AM
• I updated the table and remove the null values now it works ..... exists works strange with nulls ....
• Marked as answer by 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.

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

```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
468	Spletno gostovanje ---------> Programska oprema	Spletno gostovanje	Programska oprema	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
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	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

Monday, November 19, 2012 7:27 AM

ORDER by a.Vrsta , a.Skupina

to the queries and post again?  Thanks.

```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
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
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	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?

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.

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.

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.

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.

Monday, November 19, 2012 8:44 AM
• 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.

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  ```

Monday, November 19, 2012 8:53 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  ```

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?

Monday, November 19, 2012 9:03 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?

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.

Monday, November 19, 2012 9:14 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

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
• >I 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.

Monday, November 19, 2012 10:33 AM
• 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 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)```

Monday, November 19, 2012 3:29 PM