Query Help - 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

Transact-SQL (2000)

Query Help

Author

Topic

monty
Posting Yak Master

130 Posts

Posted - 2007-02-07 : 21:51:45

could u plz help me with this query?

First query gives me total distinct count.

1)select count(distinct([00100020])) from table
Second Query gives me total distinct by month, but if i add up the values for the month they do not equal the value from first query.

2)select datepart(mm,(convert(datetime,firstarchivedate, 103))) as "Month",
datepart(yy,(convert(datetime,firstarchivedate, 103))) as "Year",
count(distinct([00100020]))
from table
group by datepart(mm,(convert(datetime,firstarchivedate, 103))),
datepart(yy,(convert(datetime,firstarchivedate, 103)))

its me monty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 22:04:08

it may not add up to the same value.
take a look here


declare @table table
(
	[date]	datetime,
	[value]	int
)

insert into @table
select	'20070101', 10 union all
select	'20070102', 20 union all
select	'20070201', 10 union all
select	'20070201', 20 union all
select	'20070301', 30

select	[Count] = count(distinct ([value])) from @table
/*
Count       
----------- 
3
*/
select	[Year]  = datepart(year, [date]), 
	[Month] = datepart(month, [date]), 
	[Count] = count(distinct ([value])) 
from 	@table
group by datepart(year, [date]), datepart(month, [date])
/*
Year        Month       Count       
----------- ----------- ----------- 
2007        1           2
2007        2           2
2007        3           1
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 22:08:04

The problem is the count ( distinct () ). With group by, it is counting the distinct per group. Total distinct count per group will always be more than overall distinct count

I think you got confuse with count() alone or other aggregate like sum()


declare @table table
(
	[date]	datetime,
	[value]	int
)

insert into @table
select	'20070101', 10 union all
select	'20070102', 20 union all
select	'20070201', 10 union all
select	'20070201', 20 union all
select	'20070301', 30

select	[Sum] = sum([value]) from @table
/*
Sum         
----------- 
90
*/
select	[Year]  = datepart(year, [date]), 
	[Month] = datepart(month, [date]), 
	[Count] = sum([value])
from 	@table
group by datepart(year, [date]), datepart(month, [date])

/*
Year        Month       Count       
----------- ----------- ----------- 
2007        1           30
2007        2           30
2007        3           30
*/

monty
Posting Yak Master

130 Posts

Posted - 2007-02-07 : 22:08:54

khtan,
thnx for the quick help.. any specific reason as to why it wont add up to the same value?

its me monty

monty
Posting Yak Master

130 Posts

Posted - 2007-02-07 : 22:09:40

thnx alot i was really confused.. thnx again khtan

its me monty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources