?
Solved

count does not work with join?

Posted on 2012-08-17
7
Medium Priority
?
496 Views
Last Modified: 2012-11-30
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?
0
Comment
Question by:pillmill
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38305939
* refers to one table, so if you're joining on two tables you need to specify either table1.* or table2.*
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38305940
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
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38306032
You must be working on MySQL, because in SQL Server that second statement would not even compile :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 38306314
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

0
 
LVL 9

Expert Comment

by:sarabhai
ID: 38307834
select time , table2.description , count(table2.description)  
from table inner join table2
on (table.id=table2.id)
group by time, table2.description
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 38307835
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38649187
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question