Count A GROUP BY Result

Help, (Select...Group By..) Result Error
I have data like this :
ord_empno | ord_date | ord_code
00136 | 1996-07-08 | 00010
00136 | 2003-12-31 | 00020
00136 | 2004-01-01 | 00070

I'd like to select data with the latest date (row 3).

sql : SELECT ORD_EMPNO, MAX(ORD_DATE) ORD_DATE, ORD_CODE
FROM TB_PSN_ORDER
WHERE ORD_EMPNO = ?' GROUP BY ORD_EMPNO

but the result
00136 | 2004-01-01 | 00010

What's wrong with that ? It seem it takes only the first ord_code.

---

Group By Unexpectedly Reduces A Result Set.
I am selecting categories by start letter for example:

------------
SELECT a.cat_id, a.cat_name
FROM categories a
WHERE a.cat_name LIKE 'A%'
-------------

This returns 100 records - as expected - I have 100 categories that start with the letter 'A'.

Now, I want to know how many users belong to each of those categories, we have an intermediatory "allocations" table because users can belong to many categories.

-------------
SELECT a.cat_id, a.cat_name, count( `cua_cat_id` ) AS total
FROM categories a
LEFT JOIN cat_user_allocations b ON a.cat_id = b.cua_cat_id
WHERE a.cat_name LIKE 'A%'
GROUP BY b.cua_cat_id ORDER BY total ASC
-------------

So I have added a count for the entries in the allocations table, users may belong to many or no categories and so I LEFT JOIN. However this new query returns a reduced result set (85 rows).

Does the GROUP BY affect the LEFT JOIN? Why don't I see 100 rows? I would have assumed that the second select statement would have returned the same result set, with an additional `total` column but this doesn't happen?

---

No Result For Zero COUNT
The following query

SELECT COUNT(Id) FROM References
WHERE refereeId='3'
GROUP BY targetId;

returns the desired result if there are several rows that contain the
specified refereeId, which is great.

But if there are no matching rows, nothing is returned, and I was
expecting a return value of zero.

Am I using COUNT wrong?

---

Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat *

for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat?

SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20

---

Result Of COUNT In Conditional
How can I conditionally test for the number of rows returned from a query for, say, use in a stored procedure’s conditional expression?

For example, I want an INSERT to occur when

IF (SELECT COUNT(*) FROM myTable WHERE id=5) = 1

But I’ve been getting errors, and I’m guessing it has to do with the syntax of this first part of the conditional expression - how can I test the value returned by COUNT to conditionally execute some other statement?

---

Retrive Result Of Count(*)
I am using Count(*) in my Select
Code:


String sqlStr = "Select count(*) from Bulletin where StartDate<=getDate() and getDate()<=EndDate";

ResultSet resultSet = executeSQL(sqlStr);

How do I get the the result of this select statement?

---

Wrong COUNT() Result With Joins?
I'm using this query:

SELECT DISTINCT COUNT(Programme.ProgrammeID) AS Count
FROM Programme
INNER JOIN ProgrammeCategoryLink ON Programme.ProgrammeID = ProgrammeCategoryLink.ProgrammeID
INNER JOIN ProgrammeCategory ON ProgrammeCategoryLink.ProgrammeCategoryID = ProgrammeCategory.ProgrammeCategoryID
WHERE Programme.Enabled = &#391;'
Which I was expecting to return the total number of records (11) that had Enabled = 1, but it returns 21 (there aren't even that many records).

I've messed around with GROUPING etc but I just can't figure it out. (By the way, the joins need to be there even though im not using them in this instance, as sometimes filtering is on ProgrammeCategory.ProgrammeCategoryID too).

---

Non-Numeric Result From SELECT COUNT(*)
I'm using MySQL 4.1.11 and MyODBC 3.51.11.
I've a problem with SELECT COUNT(*) query.

I'm using mysql in ASP. I'm executing this query for example:

Select Count(*) as TotalMembers From Members; //with recordset("Totalmembers")
-- or --
Select Count(*) From Members; // with recordset(0)

but both of these queries aren't giving Numeric (Integer) results...
I'm cotrolling them with IsNumeric function by ASP. But it gives False result.

Also i cannot using mathematical operators such like +-*/ . Because result isn't numeric. And an error occurs.

If you know asp, could you try to use math operators with select count(*) result?

---

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 Count When Using Group By
How can I count the number of rows that match a criteria when using group by? If I just do something like ...

---

Count And GROUP BY
I have worked out the following query and it tested fine using phpmyadmin..

SELECT uset, COUNT(*) FROM user WHERE society = '$society' GROUP BY uset

The problem I have is displaying the results. In phpmyadmin the groups are displayed with the count of each, but I have no idea how I would display the results in php.

---

Count On Group
Was wondering if comebody could help me out with a count that i am wanting to do , this is the query that i am doing and its working fine for getting the info that i want.

$result = $DB->query( "SELECT name, UNIX_TIMESTAMP(date), AVG(speed) AS avg_speed, COUNT(*) AS number FROM readings group BY name ORDER BY avg_speed desc LIMIT $maxshow");
while(list($name, $date3, $speed, $count ) = mysql_fetch_row($result))

, what i also want to do is return a count of how many different hosts there are , i am returning the full count of all of them combined but i need something to reflect how many hosts , ie there is a count of 468 total tests taken but out of that there might be only say 30 different hosts that were tested.

---

Group By Count
I'm sorta having a small problem here I'm trying to do this query:

SELECT count(*) as result ,tickets_name FROM tickets_tickets t group by tickets_name having count(*) > (avg of the count(*));

and that at the end is just explaining what i want since i haven't been able to get it right so far i tried tried to use two aggregates in a row but that gives me and error or placing a sub-query there.What i want in words would be to get the names and the count of the people that have placed more tickets than the avg. Thank ahead for any info.

---

COUNT And GROUP BY
I recently built my own forum system in PHP with a MySQL back end. It's been a real learning expererience and I have learned a lot of things. One issue I am having is with developing a top ten posters list to show the last 24 hours only.PHP Code:

 $topday_sql = "SELECT t1.fp_ID, t1.fp_posterID, t1.fp_time, ";
$topday_sql .= "t2.u_ID, t2.u_contact ";
$topday_sql .= "FROM $DBTABLE22 AS t1 ";
$topday_sql .= "LEFT JOIN $DBTABLE11 AS t2 ON t1.fp_posterID=t2.u_ID ";
$topday_sql .= "WHERE fp_time>='".(time()-86400)."' GROUP BY fp_posterID LIMIT 10";
$topday_result = @mysql_query($topday_sql, $DBlink);
while ($topday_myrow = @mysql_fetch_array($topday_result)) {
    print $topday_myrow["u_contact"]." ".$totalposts."<br>";

Table one has has the post IDs, the poster ID, and the time of post. Table two has the Users ID and Users Name. Using the above statement I can get it to list 10 out of all of the users and if I remove LIMIT 10 all the users show up properly. Now I tried numerous things but I can't seem to do is get it to count how many entries there is per person. I was hoping some thing as simple as COUNT(t1.fp_ID) AS totalposts would of worked, but when the info displayes no value seems to return.

---

Distinct List Of Parents With A Child Count Result
Parent (id, name)
Child (id, parent_id, session_name)

I'm trying to write a query that will have one output row per Parent. Each row will show:
parent.id, parent.name, count of children for that parent AS children_count

Some parents have no children. Those rows could return null or 0 for the children_count field.

I know multiple methods external to SQL to solve the overall problem such as returning the join and counting where child.ids (for the same parent) is not null.

==>> but is there a way to do it in one SQL query that returns just the info I need?

---

Group By And Count, Point
data in myTable

(id) category point
(1) 1 5
(3) 2 A
(4) 2 3
(5) 2 1
(6) 1 B
I have data in myTable like the above.

The following codes produce the following results.

code1

select sum(point) as point, count(*) as count
from myTable
where category=2

result1

point count
4 3

code2

select id, sum(point) as point, count(*) as count
from myTable
where category=2
group by id
order by id desc

result2

{id) point count
(5) 1 1
I like to produce my target result below.


target result

(id) point count

(5) 4 3

---

Count Using Subquery/group By/etc?
I have a database witha table of jobs and a table of advertisers. Jobs can be assigned an advertiser from which they came, via the advertiser_id.

So what I want to do is get my result list of advertisers with a count of how many jobs for each one.

My advertisers table looks like this:

CREATE TABLE `advertiser` (
`advertiser_id` mediumint(2) unsigned NOT NULL auto_increment,
`advertiser_nicename` varchar(127) collate latin1_general_ci NOT NULL,
`advertiser_name` varchar(127) collate latin1_general_ci NOT NULL,
`advertiser_publisher` varchar(31) collate latin1_general_ci default NULL,
PRIMARY KEY (`advertiser_id`)
);
(I'm not going to normalize advertiser_publisher. :P )

And I'll just say that the jobs table looks like this for simplicity:

CREATE TABLE `jobs` (
`job_id` int(10) unsigned NOT NULL auto_increment,
`advertiser_id` mediumint(2) unsigned NULL,
PRIMARY KEY (`job_id`)
);
So I have my list of advertisers generated as such:

PHP

$advertisers = $DB->get_results( "SELECT * FROM `advertiser` ORDER BY `advertiser_publisher`, `advertiser_name` ASC" );

And I could be very silly and add a count such as this:

PHP

foreach ( $advertisers as $id => $advertiser ) { $advertisers[$id]->advertiser_jobs_count = $DB->get_results( "SELECT COUNT(advertiser_id) FROM `jobs` WHERE `advertiser_id` = $advertiser->advertiser_id", 'NUM', FALSE, TRUE ); }

(Ok dont worry about all the php in there, i think its fairly self explanatory what is going on. )

So obviously I don't want to do that because its totally ridiculous and I'm sure that there would be an sql only method of doing it.

---

Group By And Order By Using Count
I am having real problems with a query. I would usually solve this by doing one query and then another query based on the first to do the final sort (probably cheating!). I know how to do this in coldfusion, but not php.

People have told me it is possible to do what i am after but none of their suggestions have worked so far (on another forum).

Basically, I am grouping on a username and then counting the number of referrals that user has. I want to then order by, descending, the number of referrals, so the person with the most referrals comes at the top.

Query syntax tried so far is here:

SELECT user_1.username, Count(user.refid) AS CountOfrefid
FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
GROUP BY user_1.username order by Count(user.refid);

and

SELECT user_1.username, Count(user.refid) AS CountOfrefid
FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
GROUP BY user_1.username order by CountOfrefid;

and

SELECT user_1.username, Count(user.refid) AS CountOfrefid
FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
GROUP BY user_1.username order by 2;

and

SELECT distinct(user_1.username), Count(user.refid) AS CountOfrefid
FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
GROUP BY user_1.username order by 2;

---

Need To Know How To Group By To Get Correct Count
In the following sample data, I have a field called x_duration which is the duration in seconds for a particular record in the database. The table is denormalized so that one row equates to one 'play' or 'session' for a particular track.

I need to create a view to support a reporting application where, among other metrics, I need to show how many records we have where x_duration >=15 seconds and how many records we have where x_duration >=20 seconds. Additionally, I need to sum the x_duration for all records....

---

Using Group By Date And Count
Is there a way to select count(*) by grouping by date, and having multiple
date ranges?

combining...

select field,count(*) from table where datefield > 2004/1/1 and datefield <
2004/1/31
and
select field,count(*) from table where datefield > 2004/2/1 and datefield <
2004/2/29
so that the output is

field 34
field 40

---

Count Function & Group By
Getting an error saying I cannot Group on JState2

Is this a version issue? What is an alternate?
(want to show only results having greater than 2 of the same JState 's)

SELECT COUNT(JState) as JState2, JID, StateTerm
FROM JPosts2, State
WHERE State.StateAbbr=JPosts2.JState AND (`JClass` = 'Mechanics')
GROUP BY JState2 HAVING JState2 >4
ORDER BY JState

---

Multiple COUNT() And GROUP BY
I think I'm on the right track, but I could use some help. I have a simple database with one table that contains observations of animal behavior. Columns include date, time, and animal_type.
I want an output that shows the number of times each animal type was observed, grouped by date. Something like this (sorry about the formatting)

date cat dog mouse
2004-1-1 1 2 3
2004-1-2 2 3 5
2004-1-3 1 2 3

I've gotten as far as getting one column, which would be this query:
"select date, count(date) as cat from observations group by date"
Can any one tell me how to get the rest?

---

How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly.

but what i want is , i want to count the total number of rows returned by this query using mysql.

select name,count(date) from emp group by date having count(date)>3;
this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .

---

Question About Select Count(*) With Group By
suppose a hypothetical table called 'table' with one field called 'field' of
an arbitrary type.

select count(*) from table where field='value' group by field

produces no rows when run under the latest mysql. without "group by" it
produces 1 row.

is this according to the SQL standard or is this just a coincidence? in
other words, can i rely on this behaviour to deduce that there are no fields
with value 'value' in 'table'?

---

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:

---

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)

---

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

---

GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example,

I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name.
Sample:
------------------------------
id, name, dtadded
------------------------------
1 name1 2007-10-15 00:00:00
2 name2 2007-10-15 00:00:00
3 name1 2007-10-16 00:00:00
------------------------------

I need to receive 2 results (for each name)
3 - name1 - 2007-10-16 00:00:00
2 - name2 - 2007-10-15 00:00:00

If I use SQL code:

SELECT images.id, images.dtadded, images.name, count(name)
FROM images
GROUP BY `name`
ORDER BY dtadded DESC

I get results
1 - name1 - 2007-10-15 00:00:00
2 - name2 - 2007-10-15 00:00:00

It groups records with first row in database, but I need last row in table for each name.

Question: How can I order results in side group to get needed results as described above?

---

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

---

Top Record From Each Group In GROUP BY
I never could figure out a good way to do this.
I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team.
Here is the sql:
SELECT
id,
team_name,
result,
MIN(date) AS startdate,
MAX(date) AS enddate,
COUNT(*) AS games
FROM (
SELECT
t.id,
t.team_name,
sched.date,
CASE sched.draw
WHEN 1 THEN 'draw'
ELSE CASE sched.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END AS result,
(
SELECT COUNT(*)
FROM fantasy_schedule sched2
WHERE ( CASE sched2.draw
WHEN 1 THEN 'draw'
ELSE CASE sched2.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END ) <> result
AND sched2.date >= &#55614;&#57157;-01-01' AND sched2.date < &#55614;&#57158;-01-01'
AND sched2.date < sched.date
AND sched2.fantasy_league_id = 6
AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id)
) AS rungroup
FROM fantasy_schedule sched
JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id
WHERE sched.last_update < NOW()
AND sched.date >= &#55614;&#57157;-01-01' AND sched.date < &#55614;&#57158;-01-01'
AND sched.fantasy_league_id = 6
) bighonker
GROUP BY id, team_name, result, rungroup
ORDER BY enddate DESC
And a subset of what is returned:


+----+----------------------------+--------+------------+------------+-------+
| id | team_name | result | startdate | enddate | games |
+----+----------------------------+--------+------------+------------+-------+
| 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 |
| 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 |
| 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 |
| 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 |
| 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 |
| 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 |
| 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 |
| 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 |
| 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 |
| 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 |
| 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 |
| 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 |
| 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 |
| 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 |
| 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 |
| 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 |
| 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 |
| 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 |
| 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 |
| 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 |
| 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 |
| 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 |
| 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 |
| 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 |
| 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 |
| 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 |
| 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 |
| 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 |
| 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 |
| 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 |
The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself?

Here is the corresponding table schema:



+-------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| week_num | tinyint(3) unsigned | | MUL | 0 | |
| fantasy_team1_id | int(10) unsigned | | MUL | 0 | |
| fantasy_team2_id | int(10) unsigned | | MUL | 0 | |
| winner_id | int(10) unsigned | YES | | 0 | |
| date | date | | | 0000-00-00 | |
| last_update | datetime | | | 0000-00-00 00:00:00 | |
| draw | tinyint(3) unsigned | | | 0 | |
+-------------------+---------------------+------+-----+---------------------+----------------+

fantasy_team
+---------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| user_id | mediumint(8) unsigned | | MUL | 0 | |
| team_name | char(40) | | | | |
| fantasy_division_id | int(10) unsigned | YES | MUL | NULL |
+---------------------+-----------------------+------+-----+---------+----------------+

---

GROUP BY Only Displaying 1 Row Within Each Group
I have a database that consists of various sports schedules.

The part that is NOT working is GROUP BY. I am trying to group by a column titled "sport"

My current query:

SELECT * FROM `otis_sports` WHERE season = 'Fall' GROUP BY sport

---

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 &quot;Column Count Doesn't Match Value Count At Row 1&quot; ?
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?

---

How To Get One Result
I'm trying to do the following query. I have multiple records in a table that have mainly the same data, except for the date, another grouop of data different from the first one with he same caracteristics and so on.

I need to make a query that returns only the last record of each group, form instace my table is:

field1 | field2 | field3
---------------------------
data1 | data2 | data3
data1 | data2 | data4
data5 | data6 | data7
data5 | data6 | data8

and what i need to get with the query is the following:

field1 | field2 | field3
---------------------------
data1 | data2 | data4
data5 | data6 | data8

in which data4/data8 are datetime format

Anyone can help me??

---

Getting The Last Result
I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....

As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C

So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.

---

Result Set
I have a SELECT that returns a set of data


Code:

SELECT stats_d.link_id
FROM stats_d
LEFT JOIN links ON stats_d.link_id = links.id
WHERE links.id IS NULL AND stats_d.link_id IS NOT NULL



Now, i want to update stats_d.link_id (same value for all) in the result set.

---

Result Set Of Ids
I'm selecting data where the id is in a list (ie. SELECT * FROM x_table WHERE id IN (3,4,7,8,9)).
Is there a way to maintain the order of the result set based on the order of the IN list?
For example, if I constructed my IN list to be (3,2,7,6,4), my result set will be returned in that order instead of (2,3,4,6,7).

---

First Query Result Used For Second?
Is this possible / Practical?

Function1
I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB.

Function2
takes valueA & valueB and queries the database again using these values within the query.

Im still learning mysql so the code is a real mess and not working.

Could someone give a simplified example of how this could / should be done?

---

Invert Result Set
SELECT `table1_id` FROM `table1` INNER JOIN `table2` ON `table1_id` = `table2_id` GROUP BY `table1_id`
This will return a unique list of table1_id's where there is relational data from table2.

What I want to do is invert this entire result set aka get a list of table1_id's that have NO current relation to table2.

I've done this years ago in mysql 3.23 but i'm currently having a metal blank...

---

Result From Insert
I want to insert a record and read the index value created in a single query.

It is not sufficient to insert a record with one query then read the last record with another query as a different user running the same application has a chance of inserting another record before the 2nd query (to find info on the latest record) has a chance to run.

---

Location Of A Row In Result
here's the problem let's say that I have the following table (msgs):

id, cid, text
1, 1, text1
2, 1, text2
3, 2, text3
4, 1, text4
5, 2, text5
6, 2, text6
7, 1, text7

now if i executed the following query:

SELECT id, text FROM msgs WHERE cid='1';

here's what I'll get

id, text
1, text1
2, text2
4, text4
7, text7

but here's what i want to get

counter, id, text
1, 1, text1
2, 2, text2
3, 4, text4
4, 7, text7

and then I want to know the location of the row that holds the id 4, which is 3 in this example.

1st: I have mysql 3.23 on my pc, and the new version of mysql is about 17-36 MB so it's hard for me to download it on a slow intenet connection, plus I don't know whats the version of mysql on my site server, so please suggest a solution that works for version 3.23.

2nd: I don't want to create temporary tables to find the results as many users at the same time could execute the same query, so the code might create thousends of tables at the same time.

3rd: I don't wanna use PHP code for this, as the result could return thousends of results, and I don't wanna go through loop for thousends of records.

please any ideas? oh btw I want to display only 5 records but I want to know what 5 records to display depending on the location the certain row that i'm looking for.

---

Get The Row Number From A Result Set
How can I know the row number of a result?

For example i have a query like this:
"SELECT id FROM table WHERE id = 3"

So is there a way to get the row number where id = 3? I tried using 'mysql_fetch_row()' and 'mysql_affected_rows()' but just cant get what i want.

---

Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.

---

Empty Result Set
I'm running a perl script that accesses a mysql database.  I'm trying to run a simple select statement that gives me an empty result set.  The wierd thing is that if I simply replace the table name with another table name - the statement returns stuff.  Could the one table have some sort of issue with it?

---

Display More Than One Result From DB
why this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID,  count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
$row_CallsAssignedCount = mysql_fetch_assoc($CallsAssignedCount);
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

only displays one result and  not all StaffIDs?  Its meant to list all StaffIDs and the number of calls closed by that staff member.  '$row_Leader1[StaffID]' is the coloumn that lists all the client IDs on another form.  This lists all StaffIDs fine, however, this SQL command (above)only displays ONE StaffIDs result.

---

Sql Alternate Result Set
how to do a sql select that will alternate a result set.  
I have a table called account and in account I have two different seller types 'I' and 'D'. After I run a sql query I want an 'I' record to show up and then a 'D' record and then 'I', etc....

---