why this query returns diferent number of records
-
Monday, November 19, 2012 6:52 AM
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 , SkupinaThis 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.SkupinaWhy ? what I am doing wrong?
Thanks
Leposava Knez
All Replies
-
Monday, November 19, 2012 7:06 AMModerator
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- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, November 19, 2012 7:24 AM
-
Monday, November 19, 2012 7:12 AMNo, I am working on one database....
-
Monday, November 19, 2012 7:27 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:33 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:50 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 8:05 AM
it is the same :(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
-
Monday, November 19, 2012 8:16 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:26 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 2012this 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:39 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:44 AMModerator
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- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, November 19, 2012 8:45 AM
-
Monday, November 19, 2012 8:46 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.
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:53 AMModerator
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, skupinaKalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012 -
Monday, November 19, 2012 8:58 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
nothing empty -
Monday, November 19, 2012 9:03 AMModerator
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 2012Merge 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:06 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 2012Merge them? Is this a data entry mistake?
Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012the 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:10 AMmaybe is sql server bug ?????
-
Monday, November 19, 2012 9:13 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:14 AMModerator
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- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, November 19, 2012 9:14 AM
-
Monday, November 19, 2012 9:17 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.seMicrosoft 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:20 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 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- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, November 19, 2012 5:13 PM
-
Monday, November 19, 2012 10:29 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:33 AMModerator
>
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.skupinaLikely that is the source of duplicate.
Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, November 19, 2012 10:59 AM
- Marked As Answer by Squirrel30 Monday, November 19, 2012 11:28 AM
-
Monday, November 19, 2012 11:27 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 = 2011With and without the WHERE clause.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Monday, November 19, 2012 11:33 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:44 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.seNo, 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:54 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 12:10 PMNULLs are frequent source of problems and the queries agains NULLable data should be cooked very carefully.
Serg
-
Monday, November 19, 2012 3:29 PMModerator
>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- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, November 19, 2012 4:45 PM
- Marked As Answer by Squirrel30 Tuesday, November 20, 2012 9:46 AM
