Link to home
Start Free TrialLog in
Avatar of pillmill
pillmill

asked on

count does not work with join?

I use the following query to successfully obtain the number of records in each group, in a single table:

select count(*), id, time from table group by time, id

When I repeat the query with a join to a second table (to get a descriptor), count only returns one element per group:

select count(*), table2.description, time from table inner join table
  on (table.id=table2.id)
   group by time, table2.description

The tables are :

table:        table2:
index        id
id              description
time

How can I fix this?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Pill,

That should work just fine.  Keep in mind that count(*) counts the total number of rows, so what will be returned in the column is the total number of rows in the query for that grouping.  And that value will be returned on every row in that grouping.


Kent
You must be working on MySQL, because in SQL Server that second statement would not even compile :)
You may also need to count by a specific column.  Let us know if this is what you're trying to do.
IF OBJECT_ID('table1') IS NOT NULL
	DROP TABLE table1
GO

IF OBJECT_ID('table2') IS NOT NULL
	DROP TABLE table2
GO

CREATE TABLE table1 (
	ix int, 
	id int, 
	tm datetime)
GO

CREATE TABLE table2 (
	id int, 
	description varchar(100))
GO

INSERT INTO Table1 (ix, id, tm)
VALUES (1,1, GETDATE()), (1,2,GETDATE()), (2,1, GETDATE()),(2,2, GETDATE()),(2,3, GETDATE()), (4,2, GETDATE())

INSERT INTO table2 (id, description)
VALUES (1, 'foo'), (2, 'goo')

select count(table1.ix), table2.description, tm 
from table1 
inner join table2 on (table1.id=table2.id)
group by tm, table2.description

Open in new window

select time , table2.description , count(table2.description)  
from table inner join table2
on (table.id=table2.id)
group by time, table2.description
if not working then check the rows

select time , table2.description  
from table inner join table2
on (table.id=table2.id)
group by time, table2.description
Thanks for the grade.  Good luck with your project.  -Jim