MYSQL :: Count The Result Of Group

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?

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?

Count A GROUP BY Result
I'm trying to count the result of a GROUP BY query without using the php mysql_num_rows() function. Is this even possible?

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 = Ƈ'
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());

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?

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.

X IN GROUP_CONCAT ?
I have a query that, a bit simplified, looks like this:

SELECT
positions
FROM
(SELECT
tbl.id,
(GROUP_CONCAT(tbl.foo) AS positions
FROM
tablefoo AS tbl
LEFT JOIN tablebar AS bar ON bar.lostteethbaseid = tbl.id
GROUP BY tbl.id
) AS test
WHERE
NOT (15 IN (positions))
AND NOT (16 IN (positions))
See what I want to achive is to retrive all positions from a table using GROUP_CONCAT and then check if certain values are present in the list of positions. Positions could look something like 15,27,30. However

15 IN (positions)
does NOT work like

15 IN (15,27,30)
but rather like

15 IN (ཋ,27,30')
I got the query to work using LIKE:

positons LIKE '%15%'

But I would really like to use IN instead since that would be much quicker on an indexed field. Worth noting is that casting the field "positions" from BLOB to CHAR does not help.

GROUP_CONCAT LIKE 2 NOT LIKE 20 Etc?
I have used group_concat to consolidate all the 'genreids' as follows in a field...

2, 3, 4

How would i perform a search of this field, so that if i wanted to find genreid 2? But not 20, 21, etc?

At current ive been using:

....AND genreids LIKE '%2%'

However the above obviously matches too much :/

Group_concat
I am attempting to pull data from multiple tables into a resultset that
I can use as an Excel spreadsheet for reporting. Code:

GROUP_CONCAT Bug
I am using the GROUP_CONCAT() as follows:

SELECT (SELECT CONCAT(ProjectName) FROM Project WHERE
FIND_IN_SET(ProjectID, RelatedProjectID))AS RelatedProject

We have a project tracker system and we can have one project be
related to several different projects. the issue is when there is
only 1 related project associated with another project, this query
does not work. Is there some sort of bug with GROUP_CONCAT?

Group_concat
mysql 4.1
I'm trying to use the group_concat function together with IN and I get a message "Invalid use of group function"
How else can I accomplish this?
"AND user.class_id IN (0, group_concat(classes.class_id))"

Group_concat
is there some limitations on data size when doing group_concat

Something Like GROUP_CONCAT()
basically, i want my query to do something like this would do.Code:

SELECT listings.*, GROUP_CONCAT(photos.pic SEPARATOR ','), listings.office_id FROM listings, offices JOIN photos ON photos.list_id = listings.ln WHERE listings.office_id = offices.office_id AND ln = '$ln' GROUP BY photos.pic

but that doesn't work work in 4.0.x, does anyone know how i can get the same affect in mysql 4.0.x?

Issue With GROUP_CONCAT
A table looks like:

time millisec
10:02:04 234
10:02:04 567
10:04:12 003

Both time and millisec are in order by simply select. In fact, I want to use the counter to do row operations, like different between two adjacent rows. Maybe one has better idea than adding a counter.

Complement Of Group_concat
is there a complement function of the "group_concat" function?

for example:
id,items
1,'2,45,5'
2,'5,6'

=> convert into that format
id,items
1,'2'
1,'45'
1,'5'
2,'5'
2,'6'

i need to use mysql 4.1.22 (no stored procedures...)

Alternative To GROUP_CONCAT
I have a db of events with categories and locations. A form w/ checkboxes inserts into 2 tables the following:

Table 1: event
(id, title, date, description, contact)

Table 2: event_search (only foreign keys)
(event_id,location_id,type_id)

2 other tables are listing the locations and the types

Table 3: location (International, national, all 50 us states)
(id, name)

Table 4: event_type (promotional, free, concert...)
(id, name)

Some events have multiple types and locations, so this is the only way I've figured out to have a normalized db.

How can I list the events, displaying their locations and type, without using GROUP_CONCAT (only works w/ mySQL 4.1 :( )

GROUP_CONCAT Emulation
Does anyone know how to emulate the GROUP_CONCAT function for versions before it was added?

GROUP_CONCAT Crossjoin Problems
I've been searching the forums and found this thread which seems very similar to my problem, but I'm having trouble adapting it due to the complexity of the example.

Here's my demo table and problem query:

CREATE TABLE items (id INT, name CHAR(5));
INSERT INTO items VALUES (1,'test');

CREATE TABLE gallery (img_id INT, item_id INT );
INSERT INTO gallery VALUES (1,1);
INSERT INTO gallery VALUES (2,1);
INSERT INTO gallery VALUES (3,1);

CREATE TABLE gallery2 (img_id INT, item_id INT );
INSERT INTO gallery2 VALUES (10,1);
INSERT INTO gallery2 VALUES (20,1);
INSERT INTO gallery2 VALUES (30,1);

SELECT items.*,
GROUP_CONCAT(gallery2.img_id) AS gallery2,
GROUP_CONCAT(gallery.img_id) AS gallery
FROM items
LEFT JOIN gallery2 ON gallery2.item_id=items.id
LEFT JOIN gallery ON gallery.item_id=items.id
WHERE items.id = 1
GROUP BY items.id
However this returns :

id name gallery2 gallery
1 test 10,10,10,20,20,20,30,30,30 1,2,3,1,2,3,1,2,3
Could anyone help me with how to prevent the crossjoining?

LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....

GROUP_CONCAT In Subquery - Error
HTML
SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F._id = C._id
AND C.fieldC IN (1, 2, 3)
I want to incorporate it into another query:

HTML
SELECT A.fieldA, SUM(A.fieldB) AS total,
GROUP_CONCAT(A.fieldC) AS listing,
(SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id = C.i_id
AND C.fieldC IN (listing)
) AS items
FROM TABLEA A
GROUP BY A.fieldA
ORDER BY total
But hat query throws the error:

Quote:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id at line 2

GROUP_CONCAT Alternative For MySQL < 4.1 ?
GROUP_CONCAT is a great function...But have MySQL version < 4.1 and my hosting provider can't upgrade MySQL. Is there a way to get this result with a different SELECT query?

I have a list of recipes in one table, with a list of ingredients in another. Since there are a different amount of ingredients per recipe, the 2nd table has multiple ingredients per recipe.....

Group_concat Change Separator
how can I change separator in result of my group_concat?

4.0.x Equivalent/alternative For Group_Concat?
The following query works great on my test machine with mySQL 4.1.x. However, because cPanel is taking their sweet time working in mySQL 4.1, I can't use this on my website. Is there 'any' way to accomplish this same type of return in mySQL 4.0.22? Code:

Group_concat And String Functions
I am running: MySQL Distrib 5.0.15, for pc-linux-gnu on i686

I think I have run into a limitation of the GROUP_CONCAT function but I may really be doing things incorrectly. I hope you can point me in the right direction.

We have a table of installed software that includes fields for: Product, Version, OperatingSystem and MachineName. Any software product can live on several different machines and might have different versions installed on each.

I was hoping to produce a list of products, versions and an aggregate of the os/machine names that have this product installed. I tried: Code:

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Select Group_concat Doesn't Return Numbers?!?
Say that I have a table with a with of integers and a row with text. For starters, I just want to have the integers returned in one string:

select group_concat(MyIntegers) from MyTable;
This gives me a lot of questionmarks: ?????????

However, if I do this, I get the real result:
select lower(group_concat(MyIntegers)) from MyTable;
This gives me: 11,15,20,etc.

If I select the string row, no extra work is needed.
select group_concat(MyStrings) from MyTable;
I get: "blabla,hey,there" etc.

Why can't I get the numbers "as is" in the first query? I'm using the latest 5.0.19 Win32 MySQL server, running the latest ODBC driver etc. on a Windows server 2003.

Why Does GROUP_CONCAT() Function Fail In MySQL 4.1.12 In This Query?
Here are my results using MySQL 4.1.12 and I honestly don't think this is right, but can't figure out what to do:

Alternative To Group_concat For Early Versions Of Mysql
Is there an alternative to group concat, if you are using a lower version of mysql. If any one has any ideas it would be great, currently my code (thanks to some one from these forums) looks like this.

SELECT c.clientID, group_concat(ci.interestID) as interests
FROM Clients AS c
JOIN ClientInterests AS ci ON c.clientID = ci.clientID
GROUP BY c.clientID
HAVING find_in_set(1, interests)
AND find_in_set(3, interests)

Is there any way of doing this on a lower version of mysql?
Is it possible to wirte a function similar to group_concat?

Make GROUP_CONCAT Not Group Null Values
I have a complex SELECT statement that uses GROUP_CONCAT to group like values -- which has been drastically over simplified below. From what I can surmise, GROUP_CONCAT is treating NULL as a value -- which I don't want. Is there a way to make it ignore null values to produce the 'desired output' below?

TABLE: test
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a |
+------------+------------+
| NULL | b |
+------------+------------+
| 1 | c |
+------------+------------+
| 1 | d |
+------------+------------+

Current Output
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a|b |
+------------+------------+
| 1 | c|d |
+------------+------------+

Desired Output
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a |
+------------+------------+
| NULL | b |
+------------+------------+
| 1 | c|d |
+------------+------------+

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

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?

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.

Count The Result Of Group_concat?