need help with query using join and count 1

[pandatime]

Hi,

I am having problems getting the right count(*) when I join two tables. See code below:

insert into #temp (id, code, frequency)
select f.id, d.code, count(*)
from TableF f
join TableD d on (f.id = d.id)
group by f.id, d.code
order by code

What I get for count(*) is the "product" of the rows across table. So for example, if TableF has 10 rows for id=1 and TableD has 5 rows for id=1, count will be 50, not 10, like I want.

I can easily get the right count by breaking this query into 2 queries, but I want to know is there a way to accomplish this in one query?

Thanks

 

[jbenson001]

Try:

COUNT(f.<a column name in table f>)

 

[pandatime]

No, I already thought of that. Doesn't work. Still get the product.

 

[gmmastros]

Can you show some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

create table TableF (id int)
create table TableD (id int, code varchar(4))

insert into TableF values (1)
insert into TableF values (2)
insert into TableF values (2)

insert into TableD values (1, 'ABC')
insert into TableD values (1, 'ABC')
insert into TableD values (1, 'ABC')
insert into TableD values (1, 'ABC')
insert into TableD values (1, 'ABC')

insert into TableD values (2, 'DEF')
insert into TableD values (2, 'DEF')
insert into TableD values (2, 'DEF')
insert into TableD values (2, 'DEF')
insert into TableD values (2, 'DEF')

select f.id, d.code, count(f.id)
from TableF f
join TableD d on (f.id = d.id)
group by f.id, d.code
order by code

returns:
id code (No column name)
1 ABC 5
2 DEF 10

Which when you consider it, is right. The question is, why group on id AND code? Because I want the code to show up in the result set, but I only want the count of ID, in this case 1 for "ABC" and 2 for "DEF". That is, I want my result set to look like:

id code (No column name)
1 ABC 1
2 DEF 2

So my question, was, is there a way to do this in one query?

Thanks

 

[gmmastros]

That's weird data. I mean... I'm sure you have your reasons, but it's not clear to me why you would have it that way. Anyway, this query returns your expected results based on your sample data.

Declare @TableF Table(id int)
Declare @TableD Table(id int, code varchar(4))

insert into @TableF values (1)
insert into @TableF values (2)
insert into @TableF values (2)

insert into @TableD values (1, 'ABC')
insert into @TableD values (1, 'ABC')
insert into @TableD values (1, 'ABC')
insert into @TableD values (1, 'ABC')
insert into @TableD values (1, 'ABC')

insert into @TableD values (2, 'DEF')
insert into @TableD values (2, 'DEF')
insert into @TableD values (2, 'DEF')
insert into @TableD values (2, 'DEF')
insert into @TableD values (2, 'DEF')

Select Distinct AliasF.Id, d.Code, AliasF.TheCount
From   (
       Select f.id, Count(*) As TheCount
       From   @TableF F
       Group By F.ID
       ) As AliasF
       Inner Join @TableD D
         On AliasF.Id = D.Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Thanks!

That data is just "sample", so no it doesn't make sense