MYSQL :: Query Count

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

COUNT() 2 Tables In 1 Query
So I have these two queries:

$result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

$result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

They output data like
Month Year, Total Views
So I can see how many total times a page was visited that month for a specific user_id.

The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked.

I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together.

Is it not possible to do a count() on two tables in 1 query?

Here's one of the many combined I tried.

$sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)";

Can anyone help me here or is it not possible to do it in 1 query?

Query To Return Two Count()s
I'm trying to figure out a better way of doing this:
SELECT
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 1
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event1,
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 2
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event2
As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit:

SELECT COUNT(EventID=1) AS Event1,
COUNT(EventID=2) AS Event2
FROM GuestEvents
WHERE EventStatus = 'y'
AND UserID = 'bob'

WHERE COUNT MySQL Query
PHP

$queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10";

Okay, there's my MySQL query.

I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value.

As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10.

I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes.

While I could come up with a php solution, I'd prefer to use MySQL.

I have tried this:

PHP

$queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC ";

But I keep getting a "Invalid use of group function" error.

Count Query Problem
I want to count users by their status, following is the code which I am using currently, instead of using four queries I want to convert this code in one query....

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

Count Different Distincts In One Query
Any way to count different distincts in one query?

Sub-queries not supported in my MySQL v4.0.??

I have this:

Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image.

SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage
FROM tblBooks
INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID
GROUP BY tblBooks.BookID

Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records.

The query is quite simple
SELECT COUNT(*) FROM TblName WHERE Something = 1
I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?

Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time.

However, if an event didn't happen say for 2AM then 2AM isn't shown in the return.

How can I get 2AM to show as a row, but with a count of 0 (zero) ??

Here is my qeury...

select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;

MySQL Query COUNT
I need to create a sql statement that queries a mysql database for the top x amount of entries that include their name. Basically the site logs everytime they upload a file, and I want to see which users have the top number of uploads. Any ideas? Thanks in advanced to any help.

Table:

fileid
filename
submittername
submitterip
fileinfo
filetype
date

Help With Query: Order By Association Count
Allright, here are my tables:
Posts:
id | title | text
Comments
id | post_id | name | comment
Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?

Select Query With Character Count
can you help me with creating a select query where the character count in a specific column is more than, say, 20 characters?

SQL Query To Include Count From Associated Table
I am having a tough time coming up with the sql for the statement but the long and short of it is this:

Two Tables:

Table 1 is a group - Table 2 is a list of items that belong to a group from table 1.

I want to query all (*) information from table 1 AND get the count of the number of associated items from that group from table 2 in that same query.

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

First Group By Date, Then Count... All In One Query?
I have a mySQL database that has individual records for each time a page on my site is hit. What I'm trying to do is set up a tracking system which will display the hit counts for each page, by month, for the past three months, and ordered by total hits over that timespan.

The table is simple:
visitedPage | visitedTime

My current query is this:

Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link.

I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:

Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that?

SELECT date FROM clicks
WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]'
AND link_id = '{$row['ad_linkid']}'

Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this:

SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id
then in php I initialise a loop go over the results:
PHP

while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];

and then an insert query:

INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)
while this is all dandy, I don't want duplicate data. My only current method would be
PHP

$get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id");
                while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];
$check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0);
if ($check == 0) {
                    $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)");
}
                }

as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine

Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa'

While executing these queries from front end VB, It takes above 5 secs with 50 thousand records.

How can I improve speed for this kind of queries. Is there any alternation for this command.

Query The Article Table And Get A Count Of All Related Comments
Could someone please help me with this query. I have two tables -- one for my articles and another for my comments. Comments are stored in the comments table with a corresponding article id.

I want to query the article table and get a count of all related comments. The following query doesn't seem to work for me. Can you please suggest how I fix it?

SELECT a.id AS id, a.title AS title, a.preview AS preview, a.thumbnail AS thumbnail, a.category AS category, a.timestamp AS timestamp, a.game AS game, count( b.id ) AS commentcount
FROM article_table AS a, comment_table AS b
LEFT JOIN comment_table ON a.id = b.article
WHERE a.news = 'Yes'
AND a.type = Ƈ'
AND a.saved != Ƈ'
GROUP BY b.article
ORDER BY a.timestamp DESC
LIMIT 5

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Sub Query And Count Query
i have a database with the following structure

id | MoveDate | ItemId | SiteID

(a new entry is entered when an item is moved from 1 site to another)

and i am trying to forumlate a query so that i can count how many days each item was at a specific location
so lets assumes i have the following data

1 | 01/01/2007| 1 | 1
2 | 03/01/2007| 1 | 2

how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5

Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem:

Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20")

Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day.

The site is running on Windows and coded in ASP but is calling a separate mySQL server.

Count User Comments In Each Category (was "help On A Query")
I would really appreciate if someone could help me out on this one. this are my tables:

comments

Comment_ID
Video_ID
Comment_Text
Comment_UserID
....
video
Video_ID
Category_ID
.....
category
Category_ID
Category_Name

I'm trying to get a user's count of comments for each category, ordered by the number of comments DESC. basically, I want to show the top categories where the user has posted the highest number of comments.

so far this query gets me the category name of where the user has posted:

PHP

$SQLTemp = "SELECT Category_Name
            FROM category
            WHERE Category_ID
            IN (

            SELECT DISTINCT (    Category_ID        )
            FROM video
            WHERE Video_ID
            IN (

            SELECT DISTINCT (    Video_ID        )
            FROM comments
            WHERE Comment_UserID = $id
            )
            )";

THANKS!!

Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1

I have a table 'orders' with the following fields:
ipaddress
pagenum
xtime (which is just the yearmonthday)

this is the query:
SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc

What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.

#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
)
)

Add COUNT(*) To Already Existing Query (was "How To Do This")
I have this query:
PHP

SELECT entries.*, categories.*, uploads.*, entries.blog_id FROM entriesINNER JOIN categories ON (categories.category_id = entries.category_id) LEFT JOIN uploads ON(uploads.blog_id = entries.blog_id) LEFT JOIN images ON (images.blog_id = entries.blog_id) ORDER BY entries.date_submitted DESC LIMIT 3

Now i want to add this
PHP

SELECT COUNT(*) FROM comments

but i dont know how to add it into the already existing query. The messages table has a column blog_id column in it so i could do a join like thisd LEFT JOIN comments ON
PHP

(comments.blog_id = entries.blog_id)

but i dont know how to get the first bit in there

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:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

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.

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

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: