MYSQL :: Count(*) As Count And TotalCount Of Count

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Difference Between Count(*) And Count(1)
What is the difference between count(*) and count(1)?

Difference Of Count(*) From Count(1)
Is it true that count(1) is more efficient than count(*)? They say that count(*) still goes through all the records without needing to.

How To Fix "Column Count Doesn't Match Value Count At Row 1" ?
I've been trying to install a portal to my installation of Invision Power Board, and I keep coming up with this problem. I post here for two reasons: firstly is that no one is responding over at the portal creator's thread and I need help, second is that this error looks like a mySQL error that could be solved without the need of the creator if possible.

Here is the error returned:

mySQL query error: INSERT INTO `ibf_portal_box` VALUES (1, 'lang.last_topics', '<box><menu>10,20,30,50</menu>
<topics_show>10</topics_show>
<order>last_post</order>
<exforums></exforums>
</box>', 0, 'middle', '*', 0, 4, 'last_topics', '1')

mySQL error: Column count doesn't match value count at row 1
mySQL error code:
Date: Tuesday 21st of December 2004 08:43:10 PM

I am very new to mySQL and don't really understand it. What is wrong with the above that it is trying to insert, and how is it fixed?

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()?
Im trying to count the # of records with the same field (Name) is this the way to do it?

PHP

$Count = mysql_query(" SELECT count(ID), Name FROM REVIEW WHERE Name = '".$Name."'") or die(mysql_error());

Then to display the count all i need to do ia

PHP

echo $Count;

Count()
In my database I have Racing Events and Racing Results.

In the result database each racer that entered the race is giving a place.

I am trying to pull the each total number of races racer with id 1 was in.

Say the Count was 20. I also want to get the total number of people that raced in the same events as him. Say each race had 10 people that count would be 200.

In additional to that I want to count the number of time the racer with id 1 won money.

So say that field was called winnings - Winnings > 0

Add One To Count
im sure this is very simple and that its a stupid moment i am having.

I have a column called db_count and im looking to increment it by one... I have tried this and it wont work, but i cant tell if its the other code i have that wont or this,

is this the way to do it?

UPDATE tbl_makes
SET db_count= db_count +1
WHERE make_name=཈'

Using LIKE Within COUNT?
I am having problems with this query. I have it looking at a table of products sold data - specifically shoes. With shoes there is always the problem of fishing out the data for different sizes.

With the query below I am not sure whether I am able to use LIKE within the COUNT function.

I basically want the query to return a table showing the headings Product which contains all the products (grouped) and COUNTs the number of times each product size comes up.

Here is an example:

+-------------+-----------+-----------+------+-----+-------+
| Product | UK Kids 12 | UK Kids 13 | UK 1 | UK 2 | UK ... |
+-------------+-----------+-----------+------+-----+-------+
| Product 1 | 34 | 52 | 39 | 22 | .... |
+-------------+-----------+-----------+------+-----+-------+

And so on...

Here is the query I have currently however at the moment all it seems to do is return the same number (which happens to be the total of all the sizes - i.e. the total for that shoe model) for each product accross the whole row.

SELECT
SUBSTRING_INDEX(Product,',',1) AS 'Product',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 12%') AS 'UK Kids 12',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 13%') AS 'UK Kids 13',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%1%(') AS 'UK 1',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%2%(') AS 'UK 2',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%3%(') AS 'UK 3',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%4%(') AS 'UK 4',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%5%(') AS 'UK 5',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%6%(') AS 'UK 6',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%7%(') AS 'UK 7',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%8%(') AS 'UK 8',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%9%(') AS 'UK 9',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%10%(') AS 'UK 10',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%11%(') AS 'UK 11'
FROM items_ordered,orders WHERE (Product LIKE 'Heelys%Shoes%') AND

(items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <= &#55614;&#57158;-8-31') GROUP BY Product;

Help With Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table.

I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work.

Count () Sql How To Use It?
SELECT COUNT(*) FROM Persons WHERE _____________

"________" = I don't know what to enter

I want it count the entries of visitors
whose First Name, Last Name & Email are same

Like this

------------------------------------
First Name Last Name queries
------------------------------------
xyz abc 2
-------------------------------------

First_name
Last_name & email are table columns

Count??
$num = mysql_query("SELECT COUNT(*) FROM `shoutbox`");
echo $num;

it returns "Resource id #28" my table does not have 28 fields but the id goes up to 28 i do not know what is wrong and i am trying to get the number of fields in my table.

Trying To Do A Count
My table is used to keep history of books checked in and out. I need to compile statistics to show number of check in's and check outs each day.

I have been able to generate a report for the check in and checkouts seperately but would like to have them displayed on one report instead of two.

There is one column (status_cd) in the table that records the book status as a 'out' or 'crt'(in the shelving cart).

I merley want to count the number of out's and crt's per day and display them in the same output display. Code:

Count
I have a table full of blog posts. I dont want to create a table just to log hits so i thought i would add a column to that table called 'views'.

I want to create a SQL UPDATE to count++ this, can it be done just int eh sql?

COUNT()
I need to write a SELECT statement that will display the most popular
categories.

This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.

Here is the structure of the 2 tables:

messages table
id, title, message, category, thread, status, date_posted

categories table
id, title, sub_cat

I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;

but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.

Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?

Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table. I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work..

Count(*) And Sum(1)
I am having this interesting SQL problem with mysql Ver 12.22 Distrib
4.0.16, for apple-darwin7.0 (powerpc). For some reason count(*) does
give the right result, when I replace it with sum(1) it works.

mysql> select term, year, count(*) from course where year = 2001 group
by term, year;

+--------+------+--------------------+
| term | year | count(*) |
+--------+------+--------------------+
| Fall | 2001 | 360287970189639680 |
| Spring | 2001 | 360287970189639680 |
+--------+------+--------------------+
2 rows in set (0.00 sec)

mysql> select term, year, sum(1) from course where year = 2001 group
by term, year;
+--------+------+--------+
| term | year | sum(1) |
+--------+------+--------+
| Fall | 2001 | 5 |
| Spring | 2001 | 5 |
+--------+------+--------+
2 rows in set (0.00 sec)

Here is the schema.........

Using COUNT
I have a problem on using the COUNT Function

TableA
+------+-------+----------+
| ID | Status| BatchID |
+------+-------+----------+
| 1 | Pass | 1 |
| 2 | Fail | 1 |
| 3 | Pass | 1 |
| 4 | Pass | 2 |
| 5 | Fail | 3 |
+------+-------+----------+

QUERY Result
+--------+-------------+------------+
|BatchID | Status_Pass | NoOfRecord |
+--------+-------------+------------+
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 0 | 1 |
+--------+-------------+------------+

My Query
SELECT A.BatchID, TA.Status_Pass, COUNT(*) AS NoOfRecord
FROM TableA AS A LEFT JOIN
(SELECT BatchID, COUNT(*) AS Status_Pass FROM TableA WHERE Status = 'Pass' GROUP BY BatchID) AS TA
ON (A.BatchID = TA.BatchID)
GROUP BY A.BatchID

Help With Count
I have 2 tables.

TableA
---------
int tableAPrimaryKey

TableB
---------
int tableBPrimaryKey
int tableAForeignKey

For each row in Table A, I am trying to get a count from Table B

select a.tableAPrimaryKey, count(*) as myCount
from TableA a, TableB b
where a.tableAPrimaryKey = b.tableAForeignKey
group by a.tableAPrimaryKey

This query returns the correct results, but only if the count is >= 1. I want a count for every row in TableA, even if the count is zero (or null...which I would count as zero)

The following query accounts for nulls, but the count ends up being 'one' instead of 'zero'.

select a.tableAPrimaryKey, count(*) as myCount
from TableA a
left outer join TableB b on a.tableAPrimaryKey = b.tableAForeignKey
group by a.tableAPrimaryKey.

Getting A 0 Count
I have a query, which essentially is this.

select count(visible) as cVis, Visible
from products

Visible can have a value of 0 or 1.. and I'm trying to figure out how to always get a count on both (Invisible 0 and Visible 1).

Right now if there's no count for one of them, it doesn't return it.

Count
I have a column that has about 50 possible values that can be put into it. I am trying to generate a query that can tell me how many times each of these values has been used.
Right now it's slow and painful going through and running 50 scripts like this:

SELECT count('col3') FROM tblStuff WHERE col3='widget1'
What would be nice would be to run a script that returned something like:

widget1 = 105
widget2 = 309
widget3 = 92
...
widget50 = 110

Count()
Is it possible to use count() twice in a query if I'm joining tables to count records in each table?

COUNT Is 0
I have the following query:

SELECT u.id,u.username, COUNT(p.id) FROM '.$db->prefix.'users AS u, '.$db->prefix.'posts AS p WHERE u.referred_by = '.$id.' AND p.poster_id = u.id GROUP BY u.id

The gist of the query is to list all members that were referred by a specific member and count the number of posts those referred members have made (this is for a forum). The problem is, this query will not show results if the referred member has made 0 posts.

COUNT(*)
Seems my Host uses version 4.0.24 and it is fussy about using COUNT(*) if I am reading the error message correctly. Is there another way to make this work for this version?

LEFT OUTER
JOIN (
select Players_Player_ID
, count(*) as player_goals
from goals
group
by Players_Player_ID
) as G

Count(*)
I'd like to get a row count for a large table, but I've recently been considering if count(*) is too resource intensive? Is there a better way? A factor here might be that an exact count is not necessary, so if it's off by a few, I'm not concerned. Now, you might say "not an exact count?!?!?" You see, I was looking at SHOW TABLE STATUS, but I don't want to have to cycle through the rows to get to the table I'm interested in...

Count(*)
select count(*) from syslog.SystemEvents where message like
'%postfix/smtp[%' and (message like '%@carcass.us>%' or message like
'%@deadghost.com>%' or message like '%@phatho.com>%');
Above query returns error. Have no idea how to count something like this and
the manual is not helping. I'm thinking select it into a temp table and then
count? I have had no luck with temp tables either. They just don't seem to
work for me.

SQL Can't Count
Following on from yesterdays attempts to get the amount of days available for booking I ended up writing a function to sort out the difference in dates. So now all I need to do is basically the following:
PHP Code:

SELECT bookingDate AS dayEnd FROM booking WHERE bookingDate > 2005-10-16 AND bookingConfirmed = 2 OR bookingConfirmed = 1 ORDER BY bookingDate ASC LIMIT 1

I know damn well that the result returned should be 2005-10-22 but for some reason it returns 2004-12-27. I can see how it got to that number kind of - it has looped to the end of the table and started again at the beginning (it's a really bad database design) but I can't see why the hell it would go to there instead of 2005-10-22 which is the next date greater than 2005-10-16 that has a value other than 0 in the bookingConfirmed field.

COUNT
I'm a newbies in mysql. I need some help here. I'm doing a school project. The problem is how do i count 'A's from more than 1 table. I've tried to use this:

SELECT ((COUNT(tbl1.subject1))+(COUNT(tbl2.subject1))
FROM tbl1, tbl2
WHERE tbl1.subject1='A' AND tbl2.subject1='A';

It only count tbl1 and it *10, that mean if there is only 6 'A' it will give 60.

Count Per Day
This is my problem.
I have a field 'Tstamp' of type Timestamp. This is used in a log.
What I want to do is to check how many hits that was recieved today, yesterday and total.

Like this:
select count(*) from table_name where Tstamp = 2005-02-02
and
select count(*) from table_name where Tstamp = 2005-02-01
and
select count(*) from table_name

But that is not possile. Is there such feature available?

SQL Count
I have two tables, movies and users. The movies table has the column movies.user_id. Now I want to query for the users who have the most uploaded movies (count the most occuring user_id's in the movies table). I'm guessing I need to do some sort of count with a group by function, but I can't wrap my head around how to form the query.

COUNT
I have a select statement and I tried to use COUNT but got a big fat error.
I warn you I am using Dreamweaver so I know I am limited.
This is what I have so far:
SELECT classid, class_category, classbusinessname, stateCode, voucher
FROM (class_ad INNER JOIN regid ON class_ad.regid = regid.regid)
WHERE regid.reg_status = 'active'
ORDER BY adscreation_date DESC

but what I want it to show only if there is more than 1 class_ad.voucher
You know by not being empty.
I tried using the >= operator but it doesn't improve the situation.

I also tried AND class_ad.voucher = '1' but doesn't work I still get other ads showing without having a voucher submitted.

[PHP] Get Row Count
I have code where i get everything from one table, but i also need the row count of another table.
I have this:
"SELECT * FROM news ORDER BY date DESC LIMIT 3"

then the php later.....
$id=$row['id'];
$body=$row['body'];
$date=$row['date'];

how do i get it so i can define a variable of the count of the rows in a different table?

Count
I have a little question.I need to count something like this

person 1 has a ps3
person 2 has a ps3
person 3 has a ps3
person 4 has a ps3
person 5 has a xbox
person 6 has a xbox

the question is how many persons got an ps3

COUNT() Vs. ENUM
I'm having a problem, which I'm pretty sure is fairly generic, but I can't figure out the solution.

Assume the following table:

create table test (
category varchar(255),
status enum("on", "off")
);
insert into test values ("foo", "on"), ("foo", "off"), ("bar", "on"), ("bar", "on");
I want to select a result, returning category, count of rows, where status = "on", and count of rows, where status = "off", grouped by category. In pseudo-

select category,
count(status="on") as num_on,
count(status="off") as num_off
from test
group by category;
Which should return something like the following:

+----------+--------+---------+
| category | num_on | num_off |
+----------+--------+---------+
| foo | 1 | 1 |
| bar | 2 | 0 |
+----------+--------+---------+

Any suggestions?

Count Between Dates?
I want to run a SQL query whereby I count how many records are in a database table ie:
PHP

<?php
          $query = mysql_query("SELECT * FROM dbtable");
          $result = mysql_num_rows($query);
          echo "$result
";
?>

But how could this be modified to count ALL stories from a weekly period, but not specify specific dates - ie find out the total entries for:

- Every time it is Sunday and the time is 00:00
till
- Every time it is Saturday ending at 23:59

So this week I would be counting all dates between

Sunday 25th February at 00:00am
to
Saturday 3rd March at 23:59pm

So when it turns:

Sunday 4th March at 00:00

a new count will begin without specifying the dates?

Better Way Of Getting Row Count For Pagination
Currently I have a script that takes user input via a form and searches the member database and spits out all the matches. I show the results using pagination, 10 results per page, and the way I get it now is using 2 queries. One to get all the results and one getting the results using the LIMIT command to show the proper results. I was wondering if there is a better way through a sql statement:

PHP

$query = "SELECT firstname,lastname,email FROM members WHERE firstname LIKE '%$firstname' OR lastname LIKE '%$lastname' OR email LIKE '%$email%'"; //I then get the number of rows found from the query, then$paginate = new Pagination($numofrows,$resultperpage,$start); $query2 = "SELECT firstname,lastname,email FROM members WHERE firstname LIKE '%$firstname' OR lastname LIKE '%$lastname' OR email LIKE '%$email%' LIMIT $start,$resultperpage"; //I then loop through the resords and display them to the user

Fastest Way To Count?
Does mysql have a count function where ...? I want to count the number of rows mysql returns where something is equal to something. What's the fastest way to do this? (I'm using php + mysql)

From Unixtime To Count Per Day
I have a table of records, each having a unixtime stamp field. I want do some basic reporting, and I want to end up with the number of records per day for a given date range.

What's the best way to do this?

Count Of PROCESSLIST
Using SHOW PROCESSLIST we list all the running threads, is there a way to get the count of total threads running? As we do to get the toral records in a table with

SELECT COUNT(*) FROM table
I can get total threads using mysql_num_rows(), is there any other way?

I am using PHP 4.4 and MySQL 4.1.

Getting COUNT Without Duplicates
I was just reading about mysql_calc_rows, which I cannot rely on since I am stuck on MySQL 3.23.

I have a query that grabs products from a category and all of its subcategories. The category tree is maintained as a preorder tree with each node having a left and right value (makes it easy to grab all products in all subcategories).

Here is the relevant setup:

create table categories_list(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
lval INT NOT NULL,
rval INT NOT NULL
);

create table product_categories(
prod_id INT NOT NULL,
cat_id INT NOT NULL,
PRIMARY KEY(prod_id, cat_id)
);

create table products(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
[product fields]
);
Products can belong to more than one category. Here is the query I use to get all products contained within the current category tree.

SELECT DISTINCT $_PRODUCT_FIELDS
FROM
product_categories as pc
LEFT JOIN
products as p ON p.id=pc.prod_ID
INNER JOIN
categories_list as cl ON cl.id=pc.cat_id
WHERE
cl.lval >= $parent_left AND
cl.rval <= $parent_right
LIMIT $start_row, $rows_per_page
If I didn't have that LIMIT clause in there, I would be happy to just use the PHP function for counting num rows returned. I want to show something like "Displaying rows 11-20 of 512". So I need to know how many rows the query would return without the LIMIT clause.

I suppose I could run the query twice, once without the LIMIT, and then count all the rows, but with 20,000 products in the database I don't really want to shuffle all that data between PHP and MySQL.

I would do something like:

SELECT COUNT(*)
FROM
product_categories as pc
LEFT JOIN
products as p ON p.id=pc.prod_ID
INNER JOIN
categories_list as cl ON cl.id=pc.cat_id
WHERE
cl.lval >= $parent_left AND
cl.rval <= $parent_right
That will give me a different number though, because duplicate rows will be counted -- if a product is in more than one category, it will be counted twice.

I may have made some typos in this post (please point out if something looks broken) but the query is working fine. I just want a way to count the rows without doing the query all over..

Sum() From One Table And Count() From Another
I'm trying to create a report and I have it working with a ton of code that joins two queries together, but I know there must be a way to do it with one query.

I have two tables

orders (id, datetime, total)

and

order_items (id, order_id, name)

Basically I'm trying to create a report that breaks things down by month that shows the total number of items ordered and the total amount made.

July 06 : 23 : $1,023.00
August 06 : 35 : $2,033.00

So I can do something like this to get the number of items:

select date_format(datetime, '%Y-%m') as month, count(order_items.id) as cnt from `order_items` join orders on orders.id = order_items.order_id group by date_format(datetime, '%Y-%m') order by month

Then I can do something like this to get the total amount made:

select sum(orders.total) as total, date_format(datetime, '%Y-%m') as month from `orders` group by date_format(datetime, '%Y-%m') order by month

How can I combine them into one query? I'm using MySQL version 4.1.20

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'

SQL Count Query
I have 3 tables:

j_photoparent (holds photo "parent" e..g "nature", "places")
j_photocat (holds photo categories, e.g. "leaves", "snow", "london eye")
j_photosmaster holds photo details

The keys are as follows:

j_photoparent.parentID
j_photocat.catID
j_photocat.parentID
j_photosmaster.photoID
j_photosmaster.catID

I have a simple SQL statement to randomly select a photo category:

SELECT jpc.catID, jpp.parentID
FROM j_photocat jpc, j_photoparent jpp
WHERE jpp.parentID = jpc.parentID
ORDER BY RAND()
LIMIT 1
What would be great though, would be if I could combine the SQL above, to also count how many photos in the "j_photosmaster" table for each randomly selected category.

Could it be done in the same statement - or would I need to do two - the first one above and then this one:

SELECT COUNT(*) FROM j_photosmaster
WHERE CatID = &catID_FROM_ABOVE
Could it be done with a sub-select?

Help With Query Using Count
I have the following query, but for some reason I can't seem to get the price count part to work.

What I want to do is the get the max and min price for each indivdiual product and I then want to count how many products of the same where found with these different prices.

The only problem is that the count part seems to be giving me the wrong totals.

For some it is giving me 783 when there is only 2 of the same products.

Here is the query that I have.

PHP

select ides,merchantes.merid,merchant,price,min(price) as minprice,max(price) as maxprice,count(price) as pricecount,rand3,title,imageurl,brand,description,pm from merchants,prodsearches where prodsearches.merid=merchants.merid and app=Ƈ' and match (brand,category) against ('"games"' IN BOOLEAN MODE) group by brand,category order by random2 asc

Quickest Way To COUNT
I currently have a query for counting the number of rows that looks something like this:

select
product_id
from
products
WHERE
MATCH(product_name) AGAINST($keywords)

$count = mysql_num_rows($result);
if($count)
return $count;
else
return false;
Would this be any better in terms of query performance?:

select
count(product_id) as numrows
from
products
WHERE
MATCH(product_name) AGAINST($keywords)

$row = mysql_fetch_assoc($result);
$count = $row['numrows'];
return $count;
Query efficiency is my main concern. Is one any better than the other?