COUNT is wrong when linking 2 tables

[johnisotank]

Hi all,

could someone tell where i've gone wrong with this pls..

My statement looks at 2 tables. tblLift and tblJobMethods.

the tables link on field JobNo.

I want to pull in the Product field and then count how many times that product appeared for jobs beginning DA and then jobs beginning RE.

SELECT Product,
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN  a.Product END) AS 'DA Count',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN  a.Product END) AS 'DA Count'
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
GROUP BY a.Product

There is just one record in tblLift (JobNo = RE1, Product = ACETONE).
There are 4 records in tblJobMethods

So the query result should be:
Product, DA, RE
ACETONE, 0, 1

but it is actually saying
Product, DA, RE
ACETONE, 0, 4

I think DISTINCT needs adding to this but wherever I put it it keeps returning errors.

Thanks
John

 

[Geoka]

Try this code. I haven't tested it.

SELECT Product,
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN  1 else 0 END) AS 'DA Count',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN  1 else 0 END) AS 'DA Count'
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
GROUP BY a.Product

George

 

[Geoka]

Correction. The second field should title should be 'RE Count'. Same name for 2 fields are not possible.

SELECT Product,
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN  1 else 0 END) AS 'DA Count',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN  1 else 0 END) AS 'RE Count'
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
GROUP BY a.Product

George

 

[johnisotank]

Hi George, thanks for the reply.

I can't hard code a '1' in because the count is exactly that, a count.

So lets say there were 2 RE jobs both with product ACETONE the output should read
Product, DA, RE
ACETONE, 0, 2

The query is counting the linked JobMethod records and adding that to the total count. It needs to ignore them.

thanks
John

 

[IanWaterman]

I think this will achieve what you want.

SELECT Product,
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN a.Product END) AS 'DA Count',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN a.Product END) AS 'DA Count'
from
(
select distinct a.product, a.jobNo
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
) a
GROUP BY a.Product

Ian

 

[johnisotank]

Hi Ian, thanks a lot for that.

I have a feeling that might work but my query is slightly more complicated - contains a SUM but I can't figure out how to get that to work..

Here is what it actually looks like:

SELECT a.Product,
ISNULL(SUM(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN b.Price * b.Quantity END),0) AS 'DA Rev',
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN  a.Product END) AS 'DA Count',
ISNULL(SUM(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN b.Price * b.Quantity END),0) AS 'DA Rev',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN  a.Product END) AS 'DA Count'
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
GROUP BY a.Product

So the output looks like this

Product DA Rev, DA Count, RE Rev, RE Count
ACETONE £0.00 0 £50.00 1

Using your code, it doesn't like the b. column.

Apologies for not show the correct version, I didn't think I needed to get that complicated.

Thanks
John

 

[IanWaterman]

Try

SELECT Product,
Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN a.Product END) AS 'DA Count',
Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN a.Product END) AS 'RE Count'
sum(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN a.Price END) AS 'DA Count',
sum(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN a.Price END) AS 'RE price'
from
(
select a.product, a.jobNo, isnull(sumb.quantity*b.price),0) as price
FROM tblLift a LEFT JOIN
tblJobMethods b ON a.JobNo = b.JobNo
group by a.product, a.jobNo
) a
GROUP BY a.Product

Ian

 

[johnisotank]

Hi Ian,

thanks so much for that. working perfect now.

Cheers, much appreciated.

John