Hjælp til SELECT sætning

SELECT varenr, COUNT(*) FROM table WHERE AfvigStr = 0 GROUP BY varenr

Problemet er så nu at du ikke får dem ud som er nul ... tænke tænke.

SELECT varenr, COUNT(*) FROM
(SELECT DISTINCT varenr FROM table1 t1 -- giver en unik liste af ideer
LEFT JOIN table1 t2 ON t1.varenr = t2.varenr -- joiner så vi kan count
WHERE t2.AfvigStr = 0) as t3


Det er ikke testet ... men er ret overbevist om det må kunne laves ca. sådan :-)

min tabel hedder tbl_inspektioner og jeg har prøvet nedenstående:

SELECT varenr, COUNT(*) FROM
(SELECT DISTINCT varenr FROM tbl_inspektioner
LEFT JOIN tbl_inspektioner t2 ON tbl_inspektioner.varenr = t2.varenr
WHERE t2.AfvigStr = 0) as t3

..men det giver følgende fejl:

[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'VareNr'.


jeg er iøvrigt ikke sikker på, at jeg har forklaret opgaven helt skarpt.

eks.

tbl_inspektioner
+----+-------+----------+
| id | varenr| AfvigStr |
+----+-------+----------+
| 1  | 0001  |    0    |
| 2  | 0001  |    0    |
| 3  | 0001  |    1    |
| 4  | 0005  |    9    |
| 5  | 0001  |    0    |
| 6  | 0002  |    0    |
| 7  | 0001  |    0    |
| 8  | 0002  |    2    |
| 9  | 0003  |    6    |
| 10 | 0005  |    0    |
+----+-------+----------+

prøv at se på varenr '0001'. Der findes 4 linier hvor AfvigStr=0, men jeg skal kun have talt de sidste linier der har '0' i træk - dvs. at resultatet er ligmed '2' (ID 7 og ID 5)

håber at det er til at forstå...

hmmm, tænke tænke ... virker lidt som om dataen så er gemt i et mer eller mindre ubrugeligt format til dette, hvis det skulle være nemt, men det kan da lade sig gøre :-)

Det er sikkert et gammelt projekt.

men til overstående query, smid t2 på varenr, så kommer den sql fejl ik'.

Men for at være sikker ... det du vil have ... er følgende.
+----+-------+----------+
| id | varenr| AfvigStr |
+----+-------+----------+
| 1  | 0001  |    0    |
| 2  | 0001  |    0    |
| 3  | 0001  |    1    |
| 4  | 0005  |    9    |
| 5  | 0001  |    0    |
| 6  | 0002  |    0    |
| 7  | 0001  |    0    |
| 8  | 0002  |    2    |
| 9  | 0003  |    6    |
| 10 | 0005  |    0    |
+----+-------+----------+

til

+----+-------+----------+
| id | varenr| AfvigStr |
+----+-------+----------+
| 1  | 0001  |    0    |
| 2  | 0001  |    0    |
| 3  | 0001  |    1    |
| 5  | 0001  |    0    |
| 7  | 0001  |    0    |
+----+-------+----------+

Så er der overstående tilbage ...

Og så skal den tælle oppe fra og ned ... og så rultatet for varenr 0001 bliver så til 2 .... og det skal gøres for alle varenr ?

Tvivler på performance bliver god, men det kan godt lade sig gøre, min hjerne skal bare lige tænke ...

mvh

SELECT t1.varenr, COUNT(t2.varenr) FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr

Så skulle den være der ...

SELECT t1.varenr, COUNT(t2.varenr) FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr

Så skulle den være der ...

Hvis du vil vide hvorfor det virker ... så kør den her:
SELECT * FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2

Den tæller kun op hvis der er en værdi ... NULL tæller ik' op. og ja, så en group by på varenr

SELECT t1.varenr, COUNT(t2.varenr) FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr

Så skulle den være der ...

Hvis du vil vide hvorfor det virker ... så kør den her:
SELECT * FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2

Den tæller kun op hvis der er en værdi ... NULL tæller ik' op. og ja, så en group by på varenr

SELECT t1.varenr, COUNT(t2.varenr) FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr

Så skulle den være der ...

Hvis du vil vide hvorfor det virker ... så kør den her:
SELECT * FROM inspektioner t1
LEFT JOIN
(
SELECT varenr, id2 = MAX(id) FROM inspektioner
WHERE afvig != 0 GROUP BY varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2

Den tæller kun op hvis der er en værdi ... NULL tæller ik' op. og ja, så en group by på varenr

HURRA FOR DERES LORTE cache ... nå, men nu kom der da lidt posts fra mig ... HAHAHAH

Hej buzzzz,

Ohh...håber ikke at det har spoleret din nattesøvn!
Det gør ikke noget, at performance ikke bliver så god på denne.

Jeg har nu prøvet:


SELECT t1.varenr, COUNT(t2.varenr) FROM tbl_inspektioner t1
LEFT JOIN
(
SELECT tbl_inspektioner.varenr, id2 = MAX(tbl_inspektioner.id) FROM tbl_inspektioner
WHERE tbl_inspektioner.afvigStr=0 GROUP BY tbl_inspektioner.varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr


Jeg bruger ASP, og kan få kolonnen med varenr ud.
Hvordan få jeg kolonen med count ud som recordset?...dvs. hvordan sætter jeg et alias på dit script?

...nåå det er selvfølgelig COUNT(t2.varenr)


PERFEKT!

mange tak for hjælpen :-)

eller:
SELECT Nr = t1.varenr, C = COUNT(t2.varenr) FROM tbl_inspektioner t1
LEFT JOIN
(
SELECT tbl_inspektioner.varenr, id2 = MAX(tbl_inspektioner.id) FROM tbl_inspektioner
WHERE tbl_inspektioner.afvigStr=0 GROUP BY tbl_inspektioner.varenr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.id2
GROUP BY t1.varenr

Nej, brugte små 5 mins på lige at dreje den ... og så lidt problemer med Eksperten, som du kan se på dine mange ekstra posts.

Man kan sove når man bliver gammel :-p

...der er faktisk et problem

Det er værdierne 0 der skal tælles op, men det fikses nemt ved at ændre 'WHERE tbl_inspektioner.afvigStr=0' til 'WHERE tbl_inspektioner.afvigStr>0'

Nu tælles 0-værdierne, men kun hvis der samtidig findes en linie der har en værdi >0. Dvs. at hvis der på et varenr kun findes 0-værdier så bliver resultat 0

håber du kan hjælpe buzzz!

Det løser den første da også.

Først finder den alle ID'ers MAX som har en værdi som er forskellig fra 0 .... og det ID skal vi join ned til ... dvs alle de andre ID'er som er lavere end det skal ikke tælles med længere som du selv har beskrevet.
Derefter lave vi en GROUP BY på varenr, og tæller op hver gang den højre table ikke er NULL.

Er vi ikke enige i overstående ?

mvh

Giv mig test data som du mener giver det forkerte resultat.

mvh

hej igen,

Fx vil nedenstående retunere '0' og ikke '5' som ønsket.
+----+-------+----------+
| id | varenr| AfvigStr |
+----+-------+----------+
| 1  | 0001  |    0    |
| 2  | 0001  |    0    |
| 3  | 0001  |    0    |
| 4  | 0001  |    0    |
| 5  | 0001  |    0    |
+----+-------+----------+

Nedenstående retunerer korrekt '3'
+----+-------+----------+
| id | varenr| AfvigStr |
+----+-------+----------+
| 1  | 0001  |    0    |
| 2  | 0001  |    1    |
| 3  | 0001  |    0    |
| 4  | 0001  |    0    |
| 5  | 0001  |    0    |
+----+-------+----------+

ahhh, 2 sek

SELECT t1.varenr, COUNT(t2.VareNr) FROM inspektioner t1
LEFT JOIN
(
    SELECT i1.VareNr, MinID = ISNULL(MinID, 0) FROM (SELECT DISTINCT varenr FROM inspektioner) AS i1
LEFT JOIN
(
    SELECT VareNr = varenr, MinID = MAX(id) FROM inspektioner j1
    WHERE afvig != 0
    GROUP BY varenr
) i2 ON i1.varenr = i2.VareNr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.MinID
GROUP BY t1.varenr

Det er bare helt perfekt!

Så kører det præcis som jeg ønsker - dvs næsten - der mangler lige en afgrænsing i WHERE betingelserne.

Jeg vil gerne give dig yderlig 200 point buzzz, hvis du kan hjælpe med at følgende:

tabellen inspektioner har yderligere to felter 'firma' og 'kontroltype'

Der skal filtreres således at firma=106 og kontroltype=2

hvordan tilføjer jeg det i dit script?

Du må nok hellere oprette et nyt spm ... henvise til det her og skrive hvad du mangler, hvis vi skal følge reglerne.

Men ... går ud fra at firmaer kan have de samme VareNr?
Hvis, så skal du bare ind over mer eller mindre alle sub quries og sortere fra, hvad du ikke skal have med.

Der er vel 3 quries hvor det skal ind.

SELECT t1.varenr, COUNT(t2.VareNr) FROM inspektioner t1
LEFT JOIN
(
    SELECT i1.VareNr, MinID = ISNULL(MinID, 0) FROM (SELECT DISTINCT varenr FROM inspektioner WHERE Firma = 106 AND type = 2) AS i1
LEFT JOIN
(
    SELECT VareNr = varenr, MinID = MAX(id) FROM inspektioner j1
    WHERE afvig != 0 AND Firma = 106 AND type = 2
    GROUP BY varenr
) i2 ON i1.varenr = i2.VareNr
) t2 ON t1.varenr = t2.varenr AND t1.id > t2.MinID
t1.Firma = 106 AND t1.type = 2
GROUP BY t1.varenr


mvh

Kanon - du er en SQL-haj

Der ligger point til dig ny spm

Men det kan sikkert laves på en bedre måde.

Dog husk at optimer den til måden dine Indexes er oprettet på.

mvh

ok - performance er faktisk super, selv om min tabel har over 45000 records, man det er jo heller ikke så mange felter vi har fat i.

Så er det jo fint, jeg kunne måske godt have frygtet det ville være langsommere, men det lyder jo til at virke fint og så er alle glade.

mvh