Back To Programming / Scripting / Coding Forum

COUNT+DISTINCT in signle MySql query

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post December 6th, 2010, 2:09 am

Hi there all, I have the following table (simplified for reference):

MYSQL Code: [ Select ]
CREATE TABLE `search`(
`id` INT(11) NOT NULL PRIMARY KEY REFERENCES `data`(`id`),
`term` VARCHAR(255) NOT NULL,
INDEX(`id`)
)ENGINE=MyISAM;
  1. CREATE TABLE `search`(
  2. `id` INT(11) NOT NULL PRIMARY KEY REFERENCES `data`(`id`),
  3. `term` VARCHAR(255) NOT NULL,
  4. INDEX(`id`)
  5. )ENGINE=MyISAM;


let's say I have the following values in there:

MYSQL Code: [ Select ]
INSERT INTO `search` VALUES(1,'RT');
INSERT INTO `search` VALUES(2,'Joe');
INSERT INTO `search` VALUES(3,'RT');
INSERT INTO `search` VALUES(4,'Joe');
INSERT INTO `search` VALUES(5,'Bill');
  1. INSERT INTO `search` VALUES(1,'RT');
  2. INSERT INTO `search` VALUES(2,'Joe');
  3. INSERT INTO `search` VALUES(3,'RT');
  4. INSERT INTO `search` VALUES(4,'Joe');
  5. INSERT INTO `search` VALUES(5,'Bill');


I am looking for a single query that will return the distinct search terms and also how many times each distinct terms comes up in the table, like the following:

RT | 2
Joe | 2
Bill | 1

So basically I'd like to comine two queries:

MYSQL Code: [ Select ]
SELECT DISTINCT(`term`) FROM `search`
JOIN `data` ON `search`.`id`=`data`.`id`
WHERE `data`.`status` != 'D';
  1. SELECT DISTINCT(`term`) FROM `search`
  2. JOIN `data` ON `search`.`id`=`data`.`id`
  3. WHERE `data`.`status` != 'D';

AND
MYSQL Code: [ Select ]
SELECT COUNT(`search`.`id`) FROM `search`
JOIN `data` ON `search`.`id`=`data.`id`
WHERE `data`.`status` != 'D'
AND `search`.`term`='RT';
  1. SELECT COUNT(`search`.`id`) FROM `search`
  2. JOIN `data` ON `search`.`id`=`data.`id`
  3. WHERE `data`.`status` != 'D'
  4. AND `search`.`term`='RT';
Let's leave all our *plum* where it is and go live in the jungle ...
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post December 6th, 2010, 2:09 am

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post December 6th, 2010, 2:46 am

Okay, I found my answer, was pretty simple really. here's the code:

MYSQL Code: [ Select ]
SELECT `term`, COUNT(`term`)
FROM `search`
GROUP BY `term`
  1. SELECT `term`, COUNT(`term`)
  2. FROM `search`
  3. GROUP BY `term`
Let's leave all our *plum* where it is and go live in the jungle ...
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post December 6th, 2010, 8:37 am

I seem to remember a MySQL optimization article published by the MySQL site itself that says DISTINCT and GROUP BY are usually related and that you should try to use GROUP BY in favor of DISTINCT whenever you can.
Strong with this one, the sudo is.

Page 1 of 1

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: ScottG and 253 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.