MYSQL :: Trying To Add A Select Count To Working Sql Statement

Select Statement With Count
I want to know how many different etries there are in one column. My table: pics{id, pic_name,category} There are many pics inside, but only a few different categories, e.g. (outside, inside, garden). Now I need a Select count on this column, and for the example above the result shoud be "3" - 3 different categories

"SELECT DISTINCT COUNT(category) FROM pics"
delivers a much higher number, it counts all rows of the table.

Doing A Select Statement With An Array To Get A Count
I posted this in the php forum and it was suggested I try it in the MySQL area instead. Sorry if this gets posted more than once.

So here is my problem. I have a series of mysql queries to allow users to set filters for the data that is returned. They are searching for clients (Site_ID) with projects (Request_ID). A single client can have multiple projects. I want to be able to count the number of projects that are returned from a query. Here is the tail end of that code...

Multiple COUNT() In SELECT Statement
SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,5) = "Small"
) OR (
LEFT(s01_Options.prompt,2) IN (28,30)
)
)

SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,6) = "Medium"
) OR (
LEFT(s01_Options.prompt,2) IN (32)
)
)
And one for Large and Extra Large...

I would obviously like to combine all 4 queries in to 1 so that I can get my 4 counts in a single,

Update Statement Not Working
Why does this simple update statement not work with a mysql version 3.23.49,
it does work when using 4.0.24..... ?

UPDATE listitem,vjbase,list SET listitem.showsec = 7 WHERE listitem.id_vjbase = vjbase.vjbase_id AND listitem.id_vjbase = 3 AND vjbase.id_viduser = 61000 AND listitem.id_list = list.list_id AND listitem.id_list = 1 AND list.id_viduser = 61000

Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

Count Statement
If I want to select a result that based on the value of count (eg.
count(*) > 5), how can I write a sql to do that?

eg.
select count(*)>5
from Flight
where OperationType = "Departure"
group by Airline, Runway
order by Date

Select As Where Not Working!

SELECT CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) AS dateavail FROM atable WHERE dateavail = &#55614;&#57158;-06-01'

(dt is a date in format dd/mm/yyyy - the concat bit works fine)

Microsoft OLE DB Provider for ODBC Drivers error ?e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.19-standard-log]Unknown column 'dateavail' in 'where clause'

Also, if I get it to work, how do I convert the 2 strings to date so I can look for < and > ?

SELECT Not Working
SELECT U.*, G.Name AS groupname, C.FirstName, C.LastName,
(Select count * from Account Acc where Acc.User_ID = U.User_ID) as Account_ID
FROM ((Users U LEFT JOIN Groups G ON U.Group_ID = G.Group_ID)
LEFT JOIN Customers C ON U.Customer_ID = C.Customer_ID)
WHERE 1 = 1

The above returns a 1064 Error on SUSE Linux MySQL 4.0.18. But on WinXP MySQL5.0 all runs fine. Can anyone give me advice on how to fix the above to work? If I remove the second select from the query all works fine.

Select Where Not Working
i've been testing on a select statement on mySql 4.0.18 with the following statement and it worked fine

$result = mysql_query("SELECT * FROM rate WHERE 1 AND country LIKE 'Kenya' LIMIT 0, 30",$db);

however i'm now using a friend's isp and they have mySql 4.0.20 and the statement above doesn't work, a select * from rate however does work.I need to select a specific country from a single table and output the results onto the screen.

How Do You Count The Same Field Twice In One Statement
I have an articles table and a comments table, each comment relates to an article. I also have a validated field in comments that's indicated by 1 or 0.

Is it possible to get the total valid and total not valid comments from one sql statement? ......

Lower_case_table_names Not Working For SELECT
We're running our current TTS application with MySQL (on Unix). All database, table and column names are in lower-case. However, we need to access this database with a new application which (for some reason) converts all table and column names in its SELECT statements to uppercase.

Since MySQL is case-sensitive on Unix, I added the statement ....

Select * From User <- Not Working
I've tried to enter through command line 'select * from user' and get the following error:

mysql> SELECT * FROM user;
ERROR 1046 (3D000): No database selected

Show Only Greater Than 0 On A Count Statement
I have this SQL :

CODESelect DISTINCT th.tid, th.tname, th.tmain, th.tcolor, (SELECT COUNT(star.stid) FROM userstars as star WHERE star.stid=th.tid AND sonuid=1) as starcount FROM thread th WHERE th.tmain=1

MySql Select Distinct Not Working?
Im using the following statement but still getting duplicate Parents ....

Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this:

basketID||orderID||productID||quantity
1||1||2||1
2||1||3||1
3||1||4||1
4||2||2||1
5||2||4||1
6||3||4||1

so product 4 appears 3 times, product 2 appears twice and product 3 appears just once

how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most

so i could say

SELECT * FROM basket GROUP BY productID;

and that would group them for me, but i want them listed like

productID
4
2
3

(as 4 has the most occurances, then 2, then 3)

Combining SELECT Statements Into One SELECT Statement.
I want to take the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN authuser
ON name.empnumber = authuser.uname AND authuser.team = 'PHQ'
ORDER BY name.lastname, name.firstname;

and the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN crew_attendance_6QJ
ON name.empnumber = crew_attendance_6QJ.empno
ORDER BY name.lastname, name.firstname;

And combine them into one query that outputs all of the results both queries would output. Then order those results.

So far I have come up with:

Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.

SELECT COUNT(*) Or SELECT .. LIMIT 1?
If the purpose of a query is to see if atleast 1 record exists which would be faster?

SELECT COUNT(*) or SELECT .. LIMIT 1?

Both would use a WHERE clause.

How To Select Count(*) And Something Else?
How do you select a count and another field without having to do two queries?

here's the query I'm using:-

SELECT
count(*) as totals, user

FROM user_posts
WHERE post='$md5'

SELECT COUNT
I'm counting rows from a mysql database able and trying to display the count. I have the following.

$count = "SELECT COUNT(*) AS newcount FROM stores";
$cr = mysql_query($count);
if (!$cr)
die(mysql_error());
if($cr){
//Make site ID now ...
$var1 = $newcount;
$var2 = P00;
$var3 = $var2.$var1;
}

The problem I have is thet $newcount seems to be empty. I have used count and not put "AS newcount" in the query but that made no difference. I have tried to echo the $newcount but again it is empty. Now, in PHP is there something else I must do to return the value of $newcount that I have missed?

SELECT Count
how to use SELECT Count.

Select *, Count
I want to perform a select query, but it doesnt return the data i have in mind. The result should be all info in 1 row with the highest date plus a total of game_id
This is what works best sofar.

SELECT * , COUNT( game_id )
FROM `battle`
WHERE game_id =4
GROUP BY game_id
ORDER BY b_date DESC
LIMIT 1

Yet this doesnt return the last date but the first. Everything else i tried the count function returned 1 even though it should be 20 for example.
How can i solve this?

SELECT COUNT(*)
I heard SELECT COUNT(*) can take a lot of resources if your counting a table with lots and lots of rows (hundred thousands, millions).
What if you add A WHERE clause to it? So something like:
SELECT COUNT(*) FROM table WHERE pid = ?
(pid is a index too btw)
IF adding a where clause, does it still scan the entire million plus rows, or only scans what is returned from the WHERE clause?

SELECT COUNT Or SUM()
Is there a difference between these two functions when your trying to gather how much a item appears in a database?
For example, which one will be better for a database w/ ~150,000 rows.

SELECT COUNT(category_id) FROM Threads WHERE category_id = '2'

Will return ~50,000 rows matching category 2

OR

SELECT SUM(category_id = '2') AS total FROM Threads
total will be ~50,000 rows also.

Select Count
I need to do the follow SQL..PHP Code:

SELECT COUNT(t.id), COUNT(p.id) FROM threads t, posts p

and add the two counts together... within the SQL query can i do this?
or do i just have to return the arrays and added the results seperatly.

Select Count Where Entries Are < 4
I want to count how many users have 4 or less entries in the table, so, so far i have
SQL
SELECT COUNT (*) AS fourLOCS FROM
(SELECT DISTINCT locsLog.locStudentID FROM locsLog)
which counts all the entries in the locsLog table and groups them by studentID so tells me how many users have made entries.
How do I limit this so it only gives me a total number of users who have made 4 or less entries. I think I'd need WHERE after the nested SELECT statement, but I don't know what I'd put in it. I tried
SQL
SELECT COUNT (*) AS fourLOCS FROM
(SELECT DISTINCT locsLog.locStudentID FROM locsLog)
WHERE fourLOCS <= 4
but had an error message (I'm doing it with an Access database with ASP and the message was 'Too few parameters. Expected 1').

Select Count(*) Timing Out
I'm doing a "select count(*) from table1" and it takes forever, more then 3 minutes, for the result to come back. I'm running MySQL 5.0.19 on RedHat, I know my table has 51M records. When I run a "show table status where name='table1';" it returns the results in a few ms and gives me a record count. My table is defined below, I've tried forcing use of each index individually and forcing no indexes and all combinations and always the same result. Server is an intel based 64bit cpu with 16GB ram with pretty much nothing else going on......

Select Count(*) Issue
I have what I think is a simple SQL command getting unexpected results.

PHP code:

$query = "select count(*) as total_rows from part_master where mfg_id = '933' and part_no = '3601' order by part_no";

$result = mysql_query($query);

The result is total_rows = 0.

I run the same SQL from the DOS command window and get:

mysql> select count(*) as total_rows from part_master where mfg_id >= '933' and
part_no = '3601' order by part_no;
+------------+
| total_rows |
+------------+
| 1 |
+------------+
1 row in set (0.01 sec)

I'm running the latest versions of Apache, MySQL and PHP on a WinXP box.

Select , Count(*) In MySQL
I write the following code in vb6 its work fine for Access and MSSQL but not work with MySQL, RecordCount return -1 always kindly help me..

Dim mms As ADODB.Connection

Set mms = New ADODB.Connection
mms.ConnectionString = "DRIVER = MYSQL ODBC 3.51 Driver;SERVER=10.48.60.183;DATABASE=mms;UID=kma;pwd=abcd
mms.Open

Set rstIWOrder = New ADODB.Recordset

SelectString = "select * from IWOrder where WON ='"
SelectString = SelectString + txtIWO.Text + "'"
rstIWOrder.Open SelectString, mms, adOpenStatic, adLockReadOnly

If rstIWOrder.RecordCount = 0 Then
MsgBox "Internal Work Order not Found in Database"

else

or

SelectString = "select count(*) from IWOrder where WON ='"
SelectString = SelectString + txtIWO.Text + "'"
rstIWOrder.Open SelectString, mms, adOpenStatic, adLockReadOnly

SELECT COUNT (*) FROM Table
Newbie: SELECT COUNT (*) FROM table

When I run the above query from the command line, it works
fine.

When I run it from my powerbasic program, I can't seem
to get a reasonsible result.

What type of result does count return?
I get something that looks like this:
[ CHR$(133) ][ CHR$(21) ][ CH...

and the type is given as -5...

What is this type?

Php, Mysql And Select Count
I use as the base an existing system, called PHPNuke. My programming work before are only on Assembler, C, C++, Perl and other Databases than MySQL. In PHPNuke and/or the MySQL Queries contained in it there are sequences for simple counting rows (without further processing). The following sequence is permanently used (as example):

$result = $db->sql_query("select * from "$prefix."_sometable where someting='$foo'");
$numrows = $db->sql_numrows($result);

I would make it rather in such a way:

$result = $db->sql_query("select count(*) as count1 from "$prefix."_sometable where someting='$foo'");
$numrows = intval($db->sql_fetchrow($result['count1']));

Now my hopefully not too stupid question. Is there a reason, why e.g. in MySQL large genuine result quantities must be formed, only in order number of lines to determine? I worked so far actually only with other databases. But it is quite possible that that makes any sense here - who knows? Knows here someone the mystery solution?

SQL - Select Where Count Equals Zero
I have a table of products and a table of ratings so people can rate the products. I would like to select all the products the user has not rated. My thoughts are to join and count returning rows where count equals zero. But I am just not sure how to do it.

I have this that does the exact opposite of what I need, this returns just the row that I rated, I need all but the rows I rated, Make sense?

SELECT products.*, COUNT(ratings.id) AS count FROM products
LEFT JOIN ratings ON ratings.rateable_id=products.id
WHERE ratings.user_id = 10
GROUP BY products.id

(10 is the hard coded user id)

How can return ALL except the rows I rated?

Select Count(*) > @foobar
i have a query as follows and is unsure whether it is mysql or php:

$result = mysqli_query("SELECT COUNT(*) > @foobar AS status ....") where
it's suppose to get a one row value of either '0' or '1' (false or true)

if i were to do a SELECT COUNT(*) FROM...
i can use the php $row["COUNT(*)"] to refer to the index.

if i were to do a SELECT COUNT(*) AS status FROM...
i can use the php $row["status"] to refer to the index.

but in this case SELECT count > @foobar AS status,
refering to $row["status"] gives a empty value. however, running the
same query in mysql command prompt produces

+------+
|status|
+------+
| 1 |
+------+

php doesn't support such select statement? even array_keys($row) returns
empty.

Select COUNT(Name) Into PCount
i wanna count the total number of records by using this sql query...
Select COUNT(Name) into pcount from agent where agentcode = 'abc123';

this return me the total number of all records...

but when use this query:

Select COUNT(Name) into pcount from agent where Name = 'goh';

it return me the correct result....

may i know what is the problem and how can i solve it ??

Select Count Problem
I have two queries that I want to combine.

select Count(flag) as Good
from table
where
flag = 'G'
Group by Fruit

select Count(flag) as Bad
from table
where
flag = 'B'
Group by Fruit

COUNT (*) Inside Select
I am doing something wrong i would like to do a count inside this select for example:

SELECT th.tid, th.tname, th.tmain
(SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid)
FROM thread as th WHERE tmain=1

Slow Select Count(*)
I have a table with 75,000 rows.

An Explain tablename shows this

'TourPartsID', 'int(10) unsigned', 'NO', 'PRI', '', 'auto_increment'
'TourPartID', 'int(10) unsigned', 'NO', 'MUL', '', ''
'TourPartTypeID', 'int(10) unsigned', 'NO', 'MUL', '0', ''
'Language', 'int(10) unsigned', 'NO', '', '0', ''
'XML', 'mediumblob', 'NO', '', '', ''

When I do a Select count(*) from table, it takes about 12secs to return my number. This is occuring in the QueryBrowser and from the .NET library when it calls the Stored Procedure that does the Select count(*)....

My only quess that is my last column is a MEDBLOB but since I am NOT indexing that, I would figure MySQL ignores it.

Select Count Syntax
im just a begginer in mysql. I have a problem on how can i place a result of a select statement query in a variable.
Here my select statement:
Select count(employee) from employee where empmonth =
1 and empyear = 2005;
This syntax is ok, it return a result of 123894 records. But what I want is to place the result in one variable that i can access inside the program so that i will run this select statement only once. I have tried this one syntax:
Select count(employee) into ll_count from employee
where empmonth = 1 and empyear = 2005;
but it return a result of zero record.

Two Table COUNT/select
I need to count the total (using SUM) from one column in one table, where the a condition is set from another table.
so far I've been striking out with this:
Code:

SELECT maxbid SUM(maxbid) FROM `probid_auctions` WHERE probid_categories.parent = 1853

Help Me With This Select Statement :)
I have two tables
1- articles table which contains the articleid and article_description
2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description

I would like to select all the articles which aren't translated yet... How can i do this in MySQL?

Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements:

Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following:

SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g

This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?

Need Help With SELECT Statement
I have 2 simple tables: emps and rprts.
Table emps has: "ID", "name" (simple string), and some other irrelevant fields.
Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean).

I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest.

For example:
emps:
id name
------------------
1 jeff
2 wanda
3 brad

rprts:
id emp_id rprt type
-------------------------------------------
1 3 df 1
2 1 ie 1
3 2 ei 1
4 3 fd 0
5 2 tg 0
6 3 gf 1
For this data, the following should be selected:

id name type
----------------------------
1 jeff 1
2 wanda 0
3 brad 1

I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.

Select Statement
SELECT *
FROM Distance, LorryDetails
WHERE cavan = (Select min (cavan) From Distance where cavan>0);

here is the results i am getting

source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position
Athlone 90 0 180 80 3 02-CN-8001 Cork
Athlone 90 0 180 80 7 00-CN-1000 Cavan
Athlone 90 0 180 80 10 02-G-8001 Galway
What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.

SELECT Statement
I am trying to search a table with a statement like the following;

"SELECT name FROM table WHERE state='$state' AND trigger=1 AND areaA='$area' OR areaB = '$area'";

The problem is I need it to find the 'area' value in one of two different colums while matching the 'state' and 'trigger'. My syntax seems to be incorrect and I dont know how to find matching for 2 values while the other 2 columns can be optional. but one must be found.

Select Statement
I have a transaction table, wich has multiple transactions for one customer. each transaction has type. I want to find all customers who do not have a particular type of transaction.

The problem I have is it seems to find a postive result for every customer because every customer has a least one transaction that is not of this type. and I want the ones who have never had a transaction of this type.

Select Statement
I have a DB with a lot of tables (of course) :-)

In one of these tables I have the following structure (almost) Table "News" - ID, username, pagetitle, content. I also have a "users" table where I have username, fullname and groupname (simplified)

I want to list out how many "posts" every user has done, sorted by group..

Example:

user 1: 226 pages - Testgroup
user 4: 115 pages - Testgroup
user 6: 29 pages - Testgroup
user 2: 1 pages - Testgroup

user 12: 226 pages - Anothergroup
user 7: 115 pages - Anothergroup
user 9: 29 pages - Anothergroup
user 3: 1 pages - Anothergroup

Can anyone give me a hint in howto setup my select statement?

SELECT Statement
I have a single table that I want to select each row and group by one field and order by another. Example:

blogger  |   blog text  |  blog date

jim           blah           11/11/07
anna          blah           11/10/07
chris         blah           11/09/07
chris         blah           11/08/07
jim           blah           11/07/07
anna          blah           11/06/07

I want to group by the blogger and date descending, BUT ALSO sort by which blogger has the newest entry My output should be this:

jim           blah           11/11/07
jim           blah           11/07/07
anna          blah           11/10/07
anna          blah           11/06/07
chris         blah           11/09/07
chris         blah           11/08/07

What is the best way to produce my desired results?

Select Statement!
I have a simple query that is similar to this one:

SELECT distinct b.username from permissions a, users b, links c where
a.user_id = b.id and c.information = 14;

In this case, it selects the all users details from table 'user' that are
permissioned to access a certain information with id = 14.

The problem is that I need to select the users that are not permissioned but
I have sql v3.23 and I canot use union.

In other words, I need to do a select b.username from users b where
b.username not in "the result" of the last select.

SELECT Statement
I have two tables say TableA and TableB. I want to be able to select
a group of entries from TableB but only display the records that meet
the search criteria and are NOT in TableA. For example.

TableA:
111,John,55
112,Joe,13
113,Matt,55
114,Shawn,55
115,Dave,13

TableB:
990,John,12345678
991,Joe,12345678
992,Matt,12345678
993,Shawn,87654321
994,Dave,87654321
995,Steve,12345678
996,Tom,12345678

I want to display a list of people from TableB belonging to group
12345678 who do not already appear in TableA's group 55. The result
should be:

Joe
Steve
Tom

I have had no luck at all finding a solution. I know this would
probably be pretty simple if I were able to nest queries within
queries but the version of MySQL we are using doesn't support this.

Trying To Add A Select Count To Working Sql Statement

I am trying to add a select count inside this sql statement, but i get this error: any ideas? CODEMicrosoft OLE DB Provider for ODBC Drivers error '80040e31' Operand should contain 1 column(s)

I am trying to add a select count inside this sql statement, but i get this error: any ideas?

CODEMicrosoft OLE DB Provider for ODBC Drivers error '80040e31'

Operand should contain 1 column(s)