473,378 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

[mysql] SELECT COUNT(*) question

Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris
Jul 17 '05 #1
6 12361
sam

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?
No.
SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;


If you explain what you want to do, may be we can help.
Jul 17 '05 #2

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris


You've not specified the table join and to use the count function you need
to use GROUP BY clause. Having said that I don't think the counter>2 will
work anyway, not allowed to use the results of group function for extraction
criteria. You'll probably need to create a temp table with the count results
and then extract the final result set from that. Sub queries may do the
trick as well but depends on what version of Mysql you have.

Rgds,
Chris
Jul 17 '05 #3
"Floris van den Berg" <fl******@wxs.nl> wrote in news:bkejov$kjt$1
@reader08.wxs.nl:
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/


SELECT products.id, count(*) as counter
FROM products, keywords
WHERE keywords.keyword in ("bla","hi","hello")
GROUP BY products.id
HAVING counter > 2

--
Peter Strömberg
C2K2 C2K3 ISCCIV02
Jul 17 '05 #4
"sam" <rb*****@caramail.com> schreef in bericht
news:bk***********@news.cybercity.dk...

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?


No.
SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;


If you explain what you want to do, may be we can help.


Okay. What i want to do is create a search page on an e-commerce website. I
have a table with products (id, title, price) and a table with keywords (id,
keyword). I have an array of strings i want to lookup in the keywords table
(and i do have a GROUP BY as gortonc correctly remarked). The idea is that a
product is only found if *all* given keywords are found in a product title.
So i count the found entries and only accept those entries that have a count
larger than the size of the keywords array.

Floris
Jul 17 '05 #5
Floris van den Berg wrote...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris

try this:

select products.id, count(*) as counter
from products, keywords
where keywords.keyword in ("bla","hi","hello")
group by products.id
having count(*) > 2
Jul 17 '05 #6
"hexkid" <he****@hotpop.com> schreef in bericht
news:da**************************@posting.google.c om...
Floris van den Berg wrote...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris

try this:

select products.id, count(*) as counter
from products, keywords
where keywords.keyword in ("bla","hi","hello")
group by products.id
having count(*) > 2


Thanks! That works!

Floris
Jul 17 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: knoak | last post by:
Hi there, I have a small question: I have a table with lots of rows in it. Of course all have a different id, but each can be assigned to a certain category. Categories correspond with 1 - 10....
0
by: Fatt Shin | last post by:
Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL...
0
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
4
by: Ross Contino | last post by:
Hello to all: I have been searching the web for examples on how to determine a median value in a mySQL table. I have reviewed the article at...
7
by: Schraalhans Keukenmeester | last post by:
X-Followup: comp.lang.php I have a PHP script that adds messages to a simple MySQL Database. (PHP 5.0.3, MySQL 4.1.1) One of the fields it stores is msgid. The new msgid is a count of all...
1
by: jlee | last post by:
I'm pretty much a newbie on mysql, and I need some help. I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4 (i386) on a server hosting an active website. The site's developer...
14
by: dottty | last post by:
Hi, i have a table that has the following fields: id, name, dept, pay 1, John, Sales, 4000 2, Peter, HR, 5000 etc. How do i count how many people there are in each dept with an sql query? ...
6
by: ojorus | last post by:
Hi! My company make several flash-based games, and I use php to communicate with mysql to provide highscore-lists. My problem is this: When I save a player's score in the mysql-table, I want to...
0
by: silversubey | last post by:
I am trying to convert mssql triggers to mysql. I am very new to Mysql and would like some help with my code. My queries work but there has to be a more efficient way to write them. I would greatly...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.