459,480 Members | 1,214 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,480 IT Pros & Developers. It's quick & easy.

# Subquery

 P: 7 Just wondering if anyone can help out with this query. I have the results of the two queries combined the way I want them, just want to tally them up so I don't have duplicate entries. I've done this so far. I'm trying to wrap this within one more query to add up the total column per SyteDesc. SELECT C.SyteDesc, Sum(1) AS Total FROM [SELECT T.SyteDesc FROM (SELECT DISTINCT SyteDesc FROM SpecLog GROUP BY SyteDesc) AS T GROUP BY T.SyteDesc]. AS T2 INNER JOIN SpecLognew AS C ON T2.SyteDesc = C.SyteDesc GROUP BY C.SyteDesc; UNION SELECT C.SyteDesc, Sum(-1) AS Total FROM [SELECT T.SyteDesc FROM (SELECT DISTINCT SyteDesc FROM SpecLognew GROUP BY SyteDesc) AS T GROUP BY T.SyteDesc]. AS T2 INNER JOIN SpecLog AS C ON T2.SyteDesc = C.SyteDesc GROUP BY C.SyteDesc; This is an example of the output, that now I'm trying to combine and simplify. You will have to image this in a table, but the number at the right hand side is a quantity. SyteDesc Total " 1 1/2"" CL1500 RF XXS FLG WN SA105N" -2 " 1 1/2"" CL1500 RF XXS FLG WN SA105N" 1 " 1 1/2"" CL2500 RTJ XXS FLG WN SA105N" -6 " 1 1/2"" CL2500 RTJ XXS FLG WN SA105N" 3 " 1 1/2"" CL900 RF XXS FLG WN SA105N" -1 " 1 1/2"" CL900 RF XXS FLG WN SA105N" 3 " 1 1/2""x1"" CL900 RF FLG SO RED SA105N" -1 " 1 1/2""x1"" CL900 RF FLG SO RED SA105N" 1 " 1 1/2""x1/4"" CL2500 R23 GASKET OVAL RING SOFT IRON" -6 " 1 1/2""x1/4"" CL2500 R23 GASKET OVAL RING SOFT IRON" 4 " 1 1/2""x1/8"" CL1500 GASKET FLEXICARB TYPE CG 316SS" -2 " 1 1/2""x1/8"" CL1500 GASKET FLEXICARB TYPE CG 316SS" 1 " 1 1/2""x1/8"" CL900 GASKET FLEXICARB TYPE CG 316SS" -1 " 1 1/2""x1/8"" CL900 GASKET FLEXICARB TYPE CG 316SS" 3 " 1 1/2""x10 1/2"" BOLT STUD SA193B7M" -4 " 1 1/2""x10 1/2"" BOLT STUD SA193B7M" 4 " 1 1/2""x3/4"" CL2500 RTJ FLG SO RED SA105N" -3 " 1 1/2""x3/4"" CL2500 RTJ FLG SO RED SA105N" 3 " 1 1/8""x6 3/4"" BOLT STUD SA193B7M" -1 " 1 1/8""x6 3/4"" BOLT STUD SA193B7M" 1 " 1 1/8""x7 3/4"" BOLT STUD SA193B7M" -5 " 1 1/8""x7 3/4"" BOLT STUD SA193B7M" 5 " 1 1/8""x7"" BOLT STUD SA193B7M" -6 " 1 1/8""x7"" BOLT STUD SA193B7M" 4 Sep 27 '07 #1