qinyan
asked on
T-SQL: running total of unique ids by date?
i am trying to find the cumulative # of ids by date:
e.g. id "1" and "2" were on day 1, id "1" "3" on day 2, "4" on day 3
i want to return 2 unique ids on day 1, 3 unique ids on day 2 and 4 unique ids on day 3.
What's the best way to do that?
Thanks!
e.g. id "1" and "2" were on day 1, id "1" "3" on day 2, "4" on day 3
i want to return 2 unique ids on day 1, 3 unique ids on day 2 and 4 unique ids on day 3.
What's the best way to do that?
Thanks!
ASKER
that's not cumulative.
Here ya go, let me know if this works for ya. What it does is count the number of id's for each date. You will want to change the field names to those that your table uses.
SELECT
date,
COUNT(*) AS num_ids
FROM
table_name
GROUP BY
date
you will need to put the ids that you want into a list.
declare @IDs varchar(5000)
select @IDs = COALESCE(@IDs + ',', '') + cast (id as varchar(5))
from info1
Where date = '1/2/2008'
group by id
select @IDs as 'IDs'
declare @IDs varchar(5000)
select @IDs = COALESCE(@IDs + ',', '') + cast (id as varchar(5))
from info1
Where date = '1/2/2008'
group by id
select @IDs as 'IDs'
ASKER
NEVER MIND, I figured it out. thx
ASKER
this is also not cumulative:
SELECT
date,
COUNT(*) AS num_ids
FROM
table_name
GROUP BY
date
--------------------------
i don't want to put them in list, i want to make them rowsets.
SELECT
date,
COUNT(*) AS num_ids
FROM
table_name
GROUP BY
date
--------------------------
i don't want to put them in list, i want to make them rowsets.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from table
group by date