SQL Grouping
-
Hi i'm trying to get the following sql statement to return a count of unique AddessID.
Here is some sample data
CategoryID,AddressID,ProductID
1,2,3
1,2,4
1,2,5
2,2,4
2,2,5
1,3,7
I want to end up with the following:
CategoryID, UniqueCountOfAddressID
1,2
2,1
Query that is not working.
SELECT CategoryID, count(AddressID)
FROM Categories
GROUP BY CategoryID, ParentID
I end up with the following results
1,4
2,2
Any help would be appreciated.
Thanks
-
declare @t table(CategoryID int,AddressID int ,ProductID int)
insert into @t
select 1,2,3 union all
select 1,2,4 union all
select 1,2,5 union all
select 2,2,4 union all
select 2,2,5 union all
select 1,3,7
select CategoryID,count(*) DistinctAddresses
from
(select
CategoryID, count(*) aa
from @t
GROUP BY addressID,categoryid) a
group by categoryid
Kindest Regards,Vasc
-
SELECT CategoryID, count( distinct AddressID)
FROM Categories
GROUP BY CategoryID
* Noel -
Thanks, the distinct in the count works for me. Simple but effective 🙂
-
Cheers!
* Noel -
As effective as the other one : )))
Kindest Regards,Vasc
-
True! just less typing
* Noel -
One thing I do not get. ParentID was included in the original query, but not in the solution(s). Is this field necessary? And if so, where is coming from, (i.e., what is the table structure and relationship)?
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply