visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: MySQL Count Question
By: Squibi U.S.A.  Date: 29/04/2003 00:00:00  English  Points: 100 Status: Answered
Quality : Excellent
Hi All,

I have several tables I'm working with. I'm trying to get a count from my content table that relates to a user table and a files2 table. This ought to be easy for one of you gurus out there but I'm kinda stumped.

Here are the details:

Perl/MySQL

Only Two Tables are being used for this query:

files2 - Stores data on the files in the system.
+------------+
| file_id |
| filename |
| title |
| description|
| location |
| mime_type |
| public |
| date |
| category |
+------------+

content - Each file can be assigned to multiple users in the system so I'm keeping a content table that has a row of data for each user_id who has access to a file_id.

| content |
+------------+
| content_id |
| user_id |
| file_id |
| client_id |
+------------+

Expected Query Results:
This query should give me a count of files in the system assigned to client_id 2 AND only if the category of the files = 'SR'. (There are 37 of them that fit this criteria.)

my $out = $dbh->prepare("SELECT COUNT(DISTINCT content.file_id)
FROM content, files2
WHERE files2.category = 'SR'
AND content.client_id='$client_id'");
$out->execute;

Actual Query Results:
This query is giving me the the total results for client_id 2 in the system which is 41. However the result set should actually equal 37 because only 37 of the 41 records have a category that equals 'SR';

So there is a problem with the count. Maybe it's not possible with the way I have the tables set up. I'm loathe to change them tho at this stage in the process.

On a related note - when I do the query to actually display the results I get the proper 37 records returned. Here is an example of that query:

my $out2 = $dbh->prepare("SELECT DISTINCT content.file_id, files2.filename, files2.title, files2.category
FROM files2, content
WHERE files2.file_id = content.file_id
AND content.client_id = '$client_id'
AND files2.category = 'SR'
ORDER BY files2.date DESC
LIMIT $records");
$out2->execute;

This returns 37 records that meet the category 'SR' for client_id 2. I need the count tho... :)

Anyone have any thoughts? Does the explanation make sense?

PS - I'm using DISTINCT because there are actually multiple entries in the content table for any particular file. IE - If three users have access to the file then there will be three records stored in the content table but of course when I'm doing a count I don't want to count the duplicate entries.

TIA
By: VGR Date: 29/04/2003 07:50:00 English  Type : Answer
ok, seems good, should work.

two remarks :
1) this can be done with a TEMPORARY TABLE in first phase and the count(*) in the second phase (two calls)
CREATE TEMPORARY TABLE totor SELECT DISTINCT content.file_id
FROM content, files2
WHERE files2.category = 'SR'
AND content.client_id='$client_id';

SELECT COUNT(*) as a from totor;

2) I heard about a bug in SELECT COUNT(DISTINCT...) in MySql before 3.23.56 (latest). See changelog
By: psadac Date: 29/04/2003 08:58:00 English  Type : Comment
doesn't seem so good,

DISTINCT is generally useless if you have a well designed database, and i think it is (at list if file_id is the primary key of file2 table).
Your mistake is that you have made an implicit cross join omitting the joining condition, so correct it using :

my $out = $dbh->prepare("SELECT COUNT(*)
FROM content INNER JOIN files2 USING(file_id)
WHERE files2.category = 'SR'
AND content.client_id='$client_id'");
$out->execute;
By: VGR Date: 29/04/2003 15:45:00 English  Type : Comment
disagree :

FROM files2, content
WHERE files2.file_id = content.file_id


there it is, your implicit JOIN condition...
By: Squibi Date: 30/04/2003 02:50:00 English  Type : Comment
Yes psadac - file_id is primary key of files2 table. However when I tried your code last night with the inner join i got 62 results even though there are only 41 records for that client of which 37 match the category 'SR'.

I manually keyed it into the shell so I know the values in the variables were correct.

my $out = $dbh->prepare("SELECT COUNT(*)
FROM content INNER JOIN files2 USING(file_id)
WHERE files2.category = 'SR'
AND content.client_id='2'");
$out->execute;

VGR - I was a little hesitant to try you solution because of the perceived overhead of creating temp tables.

I would have liked to avoid that but perhaps it's not really an issue? I don't know.

I'm going to give it a whirl later today though and I will post my results.

Thank you both so far though!!
By: VGR Date: 30/04/2003 03:07:00 English  Type : Comment
my personal experience (I'm not the only one) is that a TEMPORARY table is better for performance than a lengthy JOIN

sometimes :D
By: Squibi Date: 01/05/2003 06:04:00 English  Type : Comment
The work folks have me wrapped up in meetings... uggg... I haven't forgotten about this post/question. Will finish testing submitted answers by tomorrow and select an answer.

Sorry for the delay!
By: Squibi Date: 29/06/2003 10:53:00 English  Type : Comment
Thank you both. I had gotten sidetracked and just now started to revisit. My apologies for the delay. Your responses were both good.

The host I'm using is running MySQL Server Version: 3.23.54-log.

I'm still getting the same result set (41) when it should be 37 with the temp table solution. I think the bug you mentioned is affecting the results. I'm going to see when/if they will upgrade.

It has to be the bug because I use a similar query to actually do the select to return and it gives me the correct 30 results.

thanks again.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 311.213970 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page