Link to home
Create AccountLog in
Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Query

Hi All,

I am running the below query and the attached is the result of the query:

SELECT COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANSCRIPTID,CANDIDATESCRIPT.QUESTIONPAPERBARCODE
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE ORDER BY CANDIDATESCRIPT.SCANDATE DESC,
CANDIDATESCRIPT.PACKETID,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID DESC,
CANDIDATESCRIPT.QUESTIONPAPERBARCODE

What I want is just to display the rows in which the actualpages does not match with the expectedpagecount.?

I hope it make sense.

Cheers.
ACTUALPAGES	CANDIDATESCRIPTID	EXPECTEDPAGECOUNT	PACKETID	SCANSCRIPTID	QUESTIONPAPERBARCODE
2	255636	24	97022	5305999	B62302
2	255634	12	83930	1231999	235701
48	255633	24	99225	6122010	B62301
14	255632	20	106245	7212046	A21502
8	255618	8	108867	8438010	2336B
8	255617	8	108867	8438009	2336B
8	255616	8	108867	8438008	2336B
8	255615	8	108867	8438007	2336B
8	255614	8	108867	8438006	2336B
8	255613	8	108867	8438005	2336A
8	255612	8	108867	8438004	2336A
8	255611	8	108867	8438003	2336A
8	255610	8	108867	8438002	2336A
8	255609	8	108867	8438001	2336A
8	255608	8	108877	8437002	2336B
8	255607	8	108877	8437001	2336A
16	255630	16	108990	8439012	B804
16	255629	16	108990	8439011	B804
16	255628	16	108990	8439010	B804
16	255627	16	108990	8439009	B804
16	255626	16	108990	8439008	B804
16	255625	16	108990	8439007	B804
16	255624	16	108990	8439006	B804
16	255623	16	108990	8439005	B804
16	255622	16	108990	8439004	B804
16	255621	16	108990	8439003	B804
16	255620	16	108990	8439002	B804
16	255619	16	108990	8439001	B804

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this
SELECT COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANSCRIPTID,CANDIDATESCRIPT.QUESTIONPAPERBARCODE
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE ORDER BY CANDIDATESCRIPT.SCANDATE DESC,
CANDIDATESCRIPT.PACKETID,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID DESC,
CANDIDATESCRIPT.QUESTIONPAPERBARCODE
HAVING ACTUALPAGES <> EXPECTEDPAGECOUNT

Open in new window

Avatar of matrix_aash

ASKER

Did not work it says :

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'HAVING'.
sorry about that, pls try this
SELECT COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANSCRIPTID,CANDIDATESCRIPT.QUESTIONPAPERBARCODE
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE 
HAVING ACTUALPAGES <> EXPECTEDPAGECOUNT
ORDER BY CANDIDATESCRIPT.SCANDATE DESC,
CANDIDATESCRIPT.PACKETID,CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID DESC,
CANDIDATESCRIPT.QUESTIONPAPERBARCODE

Open in new window

No joy still it says


Msg 207, Level 16, State 1, Line 11
Invalid column name 'ACTUALPAGES'.
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account