Link to home
Create AccountLog in
Avatar of pzozulka
pzozulka

asked on

MS SQL SUM

I have attached my code, which produces the following results:

1
1
3
1
1
1
2
1
3
2
1
3
1
2
2
1
1
1
3
1
1
1
1
3
2
1
1
1
1
1
1
1
2
1
1
1
1
2
1
1
1
6
2
1
1
1
1
1
5
2
2
1
1
1
2
3
2
1
2
1
1
1
2

How do I alter my code to get me the sum of that instead. Basically the correct response I need to get is 98.
SELECT COUNT(DISTINCT J.JobNo)
FROM Jobs J JOIN JobsParties JP ON J.JobNo = JP.JobNo
            LEFT OUTER JOIN JobsPartiesSvc JPS ON JP.JobPartyNo = JPS.JobPartyNo
            LEFT OUTER JOIN Contacts C ON J.OrdContactNo = C.ContactNo
            LEFT OUTER JOIN Firms F ON C.FirmNo = F.FirmNo
            LEFT OUTER JOIN State S ON F.StateNo = S.StateNo
            LEFT OUTER JOIN Invoices I ON J.JobNo = I.JobNo
WHERE J.Status NOT IN (712,812,811) AND I.Original = 1
AND J.JobDate BETWEEN '7/1/08' and '7/15/08'
AND J.BUNo = 11
GROUP BY C.DisplayName, F.FirmName, F.Address, F.City, F.PostCode, S.StateName

Open in new window

Avatar of Dabas
Dabas
Flag of Australia image

Have you tried Select Sum (Select COUNT.... --Your Snippet here)
ASKER CERTIFIED SOLUTION
Avatar of bui_trung_hieu
bui_trung_hieu
Flag of Viet Nam image

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