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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
select time , table2.description , count(table2.description)
from table inner join table2
on (table.id=table2.id)
group by time, 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
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
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