Distinct and Count 2

[nc297]

I want to count the distinct cos.

This works just want to know if there is a way to put distinct and count together?


SELECT distinct m.cos, t.fo, Isnull(COUNT(m.cos), 0) AS penddds300
FROM t16pendmvt m JOIN t16pendall t ON m.cossn = t.cos
WHERE ( mvt_typ = 'R' ) AND (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' ) and ( Datediff(DAY, t.conv_flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, m.cos
order by t.fo

 

[gmmastros]

Try this:

Select Count(Distinct m.cos)) As penddds300



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Never mind I got it...

SELECT t.fo, Isnull(COUNT(distinct m.cos), 0) AS penddds300
FROM t16pendmvt m JOIN t16pendall t ON m.cos = t.cos
WHERE ( mvt_typ = 'R' ) AND (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' ) and ( Datediff(DAY, t.conv_flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, m.cos
order by t.fo

 

[nc297]

Thanks gmmastros I was looking it up as you were answering my question!

 

[gmmastros]

One other thing (that you didn't ask about)...

I'm not a big fan of OR's in a where clause. In your case, you can remove the OR's (and make the code more readable) by doing this:

WHERE  mvt_typ = 'R'
       AND mvt_loc LIKE '[RSV]%'
       and Datediff(DAY, t.conv_flg_cdt, Getdate()) > 300

That like pattern uses square brackets which basically means "a single character". Since there is no % before it, that part of the where clause basically says, "Where mvt_loc starts with R, S, or V".




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thanks again and it makes me type less code too!

 

[Qik3Coder]

Thanks for the tip with the LIKE.

I'll answer your question, not solve your problem