Count Distinct? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count Distinct?

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-03 : 12:04:06

How would I count the distinct number of PO and Line?

SELECT DISTINCT PO, LINE
FROM CS_OnOrderReportCard
WHERE (DATELASTRECEIVED BETWEEN '02/26/2006' AND '04/01/2006')
ORDER BY PO, LINE

For example PO# ABC has 3 lines 123
That would be a count of 3
ABC 1
ABC 2
ABC 3

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-03 : 12:15:56

Hi jrockfl,

I'm not sure I follow your example, so if the following isn't what you're after, please post the DDL, some sample data, and the result you would want for that sample data...

SELECT PO, LINE, COUNT(*)
FROM CS_OnOrderReportCard
WHERE (DATELASTRECEIVED BETWEEN '02/26/2006' AND '04/01/2006')
GROUP BY PO, LINE
ORDER BY PO, LINE

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-03 : 12:53:09

Thanks for your reply, the result I need would be the SUM(COUNT(*))

(if this could work...)
SELECT PO, LINE, SUM(COUNT(*)) As MyFinalTotal
FROM CS_OnOrderReportCard
WHERE (DATELASTRECEIVED BETWEEN '02/26/2006' AND '04/01/2006')
GROUP BY PO, LINE
ORDER BY PO, LINE

The combination of the PO and LINE make a unique value

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 13:07:35

don't you just want the count of LINE's for each PO?

select PO, count(distinct LINE) -- or maybe just count(*)
FROM CS_OnOrderReportCard
WHERE (DATELASTRECEIVED BETWEEN '02/26/2006' AND '04/01/2006')
GROUP BY PO

That will give you your
ABC, 3

if you want the total then
select sum(num) from
(select PO, num = count(distinct LINE) -- or maybe just count(*)
FROM CS_OnOrderReportCard
WHERE (DATELASTRECEIVED BETWEEN '02/26/2006' AND '04/01/2006')
GROUP BY PO
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-03 : 13:25:35

Thank you nr,

Your second query was the answer I was looking for. Sorry if i did not explain it well. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources