counting specific values of a column (multiple tables)

[furei1976]

Hi Guys,

I would to know if it is possible to count specific values of a column for multiple tables.

Count function works okay using single table like the one below:
select materialno, count(materialno) as qty
from dbo.TBL_CONTRACT (nolock)
where right(contractno,8) = '40178291'
group by materialno

query result:
000000000000303885 120
000000000000306574 120


but when this will be joined to other tables it doubles the result.

select distinct c.line_item,
b.rtmg_efno,
b.contract_no,
,CONVERT(VARCHAR(10), b.user_date, 101) AS [MM/DD/YYYY]
,count(materialno)over(partition by materialno)
from tbl_contract a (nolock)
left join tbl_project_form b (nolock)
on right(a.ContractNo,8) = b.contract_no
left join tbl_project_Target c
on b.ef_id = c.ef_id

query result:
1 40178291 06/05/2012 240
2 40178291 06/05/2012 240

the 3 tables can only be joined as specified above. if I unjoin the tbl_project_Target, the result is similar to the single table query but I need some columns on the tbl_project_Target.

are there any sql functions that you can recommend to fix this?

please advise.

thanks in advance.

furei

 

[furei1976]

Please delete this thread. I was able to figure it out. Thanks.

 

[SQLBill]

Why don't you post your solution? It might help someone else in the future.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875