query with COUNT (what am I doing wrong?)

14 Message(s) by 3 Author(s) originally posted in mysql discussion

From: panchettone

Date: Thursday, October 25, 2007

Hi, I'm trying to run

<operating system, programming> The process of carrying out
	the {instructions} in a computer program by a computer.

	See also {dry run}.

	(1996-05-13)

this query

1. <database, information science> A user's (or {agent}'s)
	request for information, generally as a formal request to a
	{database} or {search engine}.

	{SQL} is the most common {database query language}.

	2. <character> {question mark}.

	(1997-04-09)

:

--->SELECT COUNT(v.id) as C, v.title, v.desc, v.cat, u.username, t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id

but I get an error

1. A discrepancy between a computed, observed, or measured
	value or condition and the true, specified, or theoretically
	correct value or condition.

	2. <programming> A mental mistake made by a programmer that
	may result in a program {fault}.

	3. (verb) What a program does when it stops as result of a
	programming error.

	(2000-03-28)

[
#1064 - You've an error in your SQL

<language, database, standard> /S Q L/ An industry-standard
	language for creating, updating and, querying {relational
	database management systems}.

	SQL was developed by {IBM} in the 1970s for use in {System R}.
	It is the {de facto standard} as well as being an {ISO} and
	{ANSI} {standard}.  It is often embedded in general purpose
	programming languages.

	The first SQL standard, in 1986, provided basic language
	constructs for defining and manipulating {tables} of data; a
	revision in 1989 added language extensions for {referential
	integrity} and generalised {integrity} {constraints}.  Another
	revision in 1992 provided facilities for {schema} manipulation
	and {data administration}, as well as substantial enhancements
	for data definition and data manipulation.

	Development is currently underway to enhance SQL into a
	computationally complete language for the definition and
	management of {persistent}, complex objects.  This includes:
	generalisation and specialisation hierarchies, {multiple
	inheritance}, user defined {data types}, {triggers} and
	{assertions}, support for {knowledge based systems},
	{recursive query expressions}, and additional data
	administration tools.  It also includes the specification of
	{abstract data types} (ADTs), object identifiers, {methods},
	{inheritance}, {polymorphism}, {encapsulation}, and all of the
	other facilities normally associated with object data
	management.

	The emerging {SQL3} standard is expected to be complete in
	1998.

	According to Allen G. Taylor, SQL does _not_ stand for
	"Structured Query Language".  That, like "SEQUEL" (and its
	pronunciation /see'kw*l/), was just another unofficial name
	for a precursor of SQL.  However, the IBM SQL Reference manual
	for DB2 and Craig Mullins's "DB2 Developer's Guide" say SQL
	_does_ stand for "Structured Query Language".

	{SQL Standards (http://www.jcc.com/sql_stnd.html)}.

	{An SQL parser
	(ftp://ftp.ora.com/published/oreilly/nutshell/lexyacc/)} is
	described in "Lex & Yacc", by Levine, Mason & Brown published
	by O'Reilly.

	{The 1995 SQL Reunion: People, Projects, and Politics
	(http://www.mcjones.org/System_R/SQL_Reunion_95/)}.

	["A Guide to the SQL Standard", C.J. Date, A-W 1987].

	["SQL for Dummies", Allen G. Taylor, IDG Books Worldwide].

	(2005-11-17)

syntax

The structure of strings in some language.  A language's
	syntax is described by a {grammar}.  For example, the syntax
	of a binary number could be expressed as

		binary_number = bit [ binary_number ]

		bit = "0" | "1"

	meaning that a binary number is a bit optionally followed by a
	binary number and a bit is a literal zero or one digit.

	The meaning of the language is given by its {semantics}.

	See also {abstract syntax}, {concrete syntax}.

	(1994-10-31)

; check the manual that
corresponds to your MySQL server

1. A program which provides some service to other ({client})
	programs.  The connection between client and server is
	normally by means of {message passing}, often over a
	{network}, and uses some {protocol} to encode the client's
	requests and the server's responses.  The server may run
	continuously (as a {daemon}), waiting for requests to arrive
	or it may be invoked by some higher level daemon which
	controls a number of specific servers ({inetd} on {Unix}).

	There are many servers associated with the {Internet}, such as
	those for {HTTP}, {Network File System}, {Network Information
	Service} (NIS), {Domain Name System} (DNS), {FTP}, {news},
	{finger}, {Network Time Protocol}.  On Unix, a long list can
	be found in /etc/services or in the {NIS} database "services".
	See {client-server}.

	2. A computer which provides some service for other computers
	connected to it via a network.  The most common example is a
	{file server} which has a local disk and services requests
	from remote clients to read and write files on that disk,
	often using {Sun}'s {Network File System} (NFS) {protocol} or
	{Novell Netware} on {PCs}.  Another common example is a {web
	server}.

	[{Jargon File}]

	(2003-12-29)

version

<programming> One of a sequence of copies of a program, each
	incorporating new modifications.  Each version is usually
	identified by a number, commonly of the form X.Y where X is
	the major version number and Y is the release number.
	Typically an increment in X (with Y reset to zero) signifies a
	substantial increase in the function of the program or a
	partial or total re-implementation, whereas Y increases each
	time the progam is changed in any way and re-released.

	Version numbers are useful so that the user can know if the
	program has changed ({bugs} have been fixed or new functions
	added) since he obtained his copy and the programmer can tell
	if a bug report relates to the current version.  It is thus
	always important to state the version when reporting bugs.
	Statements about compatibility between different software
	components should always say which versions they apply to.

	See {change management}.

	(1997-12-07)

for the right syntax to use
near '.title, v.desc' at line

1. <hardware> An electrical conductor.  For distances larger
	than a breadbox, a single line may consist of two electrical
	conductors in twisted, parallel, or concentric arrangement
	used to transport one logical signal.

	By extension, a (usually physical) medium such as an {optical
	fibre} which carries a signal.

	(1995-09-29)

1
]

If I omit COUNT (but I need it...), it perfectly works.
What am I doing wrong? How could I rewrite my query? Thanks!

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:

Hi, I'm trying to run this query:
--->SELECT COUNT(v.id) as C, v.title, v.desc, v.cat, u.username, t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id
but I get an error
[
#1064 - You've an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '.title, v.desc' at line 1
]
If I omit COUNT (but I need it...), it perfectly works.
What am I doing wrong? How could I rewrite my query? Thanks!

Try:

SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id

From: panchettone

Date: Thursday, October 25, 2007

SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id

a new error:
[
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
columns is illegal if there is no GROUP BY clause
]

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:

> SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
> t.team_name
> FROM videos AS v
> INNER JOIN users AS u ON v.user_id = u.user_id
> INNER JOIN team_members tm ON tm.user_id = u.user_id
> INNER JOIN teams AS t ON tm.team_id = t.team_id
a new error:
[
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
columns is illegal if there is no GROUP BY clause
]

Well that's basic GROUP BY syntax!

SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id
GROUP BY v.title, v.desc, v.cat, u.username, t.team_name

From: panchettone

Date: Thursday, October 25, 2007

Captain Paralytic ha scritto:

Well that's basic GROUP BY syntax!

well, the fact is I do not need any group

A group G is a non-empty {set} upon which a {binary} operator
	* is defined with the following properties for all a,b,c in G:

	  Closure:     G is closed under *,  a*b in G
	  Associative: * is associative on G, (a*b)*c = a*(b*c)
	  Identity:    There is an identity element  e  such that
		       a*e = e*a = a.
	  Inverse:     Every element has a unique inverse a' such that
		       a * a' = a' * a = e.  The inverse is usually
		       written with a superscript -1.

	(1998-10-03)

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:

Captain Paralytic ha scritto:
> Well that's basic GROUP BY syntax!
well, the fact is I do not need any group by

OH, well you had better argue that out with MySQL because it says you
do!

If you aren't happy with the syntax supplied by the MySQL product

See {tuple}.

and
SQL, you had better write

1. <chat> {Unix}'s simple {talk} command and {protocol}.
	write has been largely superseded by {talk} and then {irc}.

	An enhancement, {RWP}, has been proposed.

	2. <tool> A simple {text editor} for {Windows}.

	(1998-04-28)

your own RDBMS

<database> (RDBMS - relational database management system) A
	{database} based on the {relational model} developed by
	{E.F. Codd}.  A relational database allows the definition of
	data structures, storage and retrieval operations and
	{integrity constraints}.  In such a database the data and
	relations between them are organised in {tables}.  A table is
	a collection of rows or {records} and each row in a table
	contains the same {fields}.  Certain fields may be designated
	as {keys}, which means that searches for specific values of
	that field will use indexing to speed them up.

	Where fields in two different tables take values from the same
	set, a {join} operation can be performed to select related
	records in the two tables by matching values in those fields.
	Often, but not always, the fields will have the same name in
	both tables.  For example, an "orders" table might contain
	(customer_id, product_code) pairs and a "products" table might
	contain (product_code, price) pairs so to calculate a given
	customer's bill you would sum the prices of all products
	ordered by that customer by joining on the product-code fields
	of the two tables.  This can be extended to joining multiple
	tables on multiple fields.  Because these relationships are
	only specified at retreival time, relational databases are
	classed as {dynamic database management system}.

	The first commercial RDBMS was the {Multics Relational Data
	Store}, first sold in 1978.

	{INGRES}, {Oracle}, {Sybase, Inc.}, {Microsoft Access}, and
	{Microsoft SQL Server} are well-known database products and
	companies.  Others include {PostgreSQL}, {SQL/DS}, and {RDB}.

	["Managing Data Bases, Four Critical Factors" Michael
	M. Gorman, QED Information Sciences, Inc.].

	["An Introduction To Database Systems" (6th ed) C. J. Date,
	Addison Wesley (an excellent source of detailed info)].

	["An End-User's Guide to Data Base" James Martin, Prentice
	Hall (excellent place to begin learning about DBMS)].

	(2002-06-10)

and your own SQL standard

<standard> Standards are necessary for {interworking},
	{portability}, and {reusability}.  They may be {de facto
	standards} for various communities, or officially recognised
	national or international standards.

	{Andrew Tanenbaum}, in his Computer Networks book, once said,
	"The nice thing about standards is that there are so many of
	them to choose from", a reference to the fact that competing
	standards become a source of confusion, division,
	obsolescence, and duplication of effort instead of an
	enhancement to the usefulness of products.

	Some bodies concerned in one way or another with computing
	standards are {IAB} ({RFC} and {STD}), {ISO}, {ANSI}, {DoD},
	{ECMA}, {IEEE}, {IETF}, {OSF}, {W3C}.

	(1999-07-06)

From: Jerry Stuckle

Date: Thursday, October 25, 2007

wrote in message:
wrote in message:
Captain Paralytic ha scritto:

Well that's basic GROUP BY syntax!
well, the fact is I do not need any group by
OH, well you had better argue that out with MySQL because it says you
do!
If you aren't happy with the syntax supplied by the MySQL product and
SQL, you had better write your own RDBMS and your own SQL standard!

It's more than just MySQL, Paul. That's part of the SQL standard. In
fact, MySQL has historically been much more lenient than other databases.

IIRC, the SQL standard requires that when you mix aggregate functions
with columns (or non-aggregate functions) you must GROUP BY all
non-aggregate columns or functions. MySQL has not enforced it that much
in the past.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer

<computer> A machine that can be programmed to manipulate
	symbols.  Computers can perform complex and repetitive
	procedures quickly, precisely and reliably and can quickly
	store and retrieve large amounts of data.

	The physical components from which a computer is constructed
	(electronic circuits and input/output devices) are known as
	"{hardware}".  Most computers have four types of hardware
	component: CPU, input, output and memory.  The CPU ({central
	processing unit}) executes programs ("{software}") which tell
	the computer what to do.  Input and output (I/O) devices allow
	the computer to communicate with the user and the outside
	world.  There are several kinds of memory - fast, expensive,
	short term memory (e.g. {RAM}) to hold intermediate results,
	and slower, cheaper, long-term memory (e.g. {magnetic disk} and
	{magnetic tape}) to hold programs and data between jobs.

	See also {analogue computer}.

	(1995-03-10)

Training Corp.
jstucklex@xxxxxxxxxxx
==================

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:
wrote in message:
wrote in message:
Captain Paralytic ha scritto:
> Well that's basic GROUP BY syntax!
well, the fact is I do not need any group by
> OH, well you had better argue that out with MySQL because it says you
> do!
> If you aren't happy with the syntax supplied by the MySQL product and
> SQL, you had better write your own RDBMS and your own SQL standard!
It's more than just MySQL, Paul. That's part of the SQL standard.

Errm, hence why I said "you had better write your own RDBMS and your
own SQL standard!"

From: panchettone

Date: Thursday, October 25, 2007

OH, well you had better argue that out with MySQL because it says you
do!
If you aren't happy with the syntax supplied by the MySQL product and
SQL, you had better write your own RDBMS and your own SQL standard!

ok, I did not want to start a fight, sorry for saying what I said, it was
my ignorance speaking for me. I was just wondering -since my query is
just working- if that group-by is something that can alter the way the
results are sorted by (the entire query I didn't post ends with an
order by desc)

From: panchettone

Date: Thursday, October 25, 2007

IIRC, the SQL standard requires that when you mix aggregate functions
with columns (or non-aggregate functions) you must GROUP BY all
non-aggregate columns or functions.

could you suggest a place online where I can find tips like this one?

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:

> IIRC, the SQL standard requires that when you mix aggregate functions
> with columns (or non-aggregate functions) you must GROUP BY all
> non-aggregate columns or functions.
could you suggest a place online where I can find tips like this one?

The Reference Manual!

http://dev.mysql.com/doc/refman/5.0/en/

From: Captain Paralytic

Date: Thursday, October 25, 2007

wrote in message:

> OH, well you had better argue that out with MySQL because it says you
> do!
> If you aren't happy with the syntax supplied by the MySQL product and
> SQL, you had better write your own RDBMS and your own SQL standard!
ok, I did not want to start a fight, sorry for saying what I said, it was
my ignorance speaking for me. I was just wondering -since my query is
just working- if that group-by is something that can alter the way the
results are sorted by (the entire query I didn't post ends with an
order by desc)

Jerry and I are not fighting - yet!

From: Jerry Stuckle

Date: Friday, October 26, 2007

wrote in message:
wrote in message:
OH, well you had better argue that out with MySQL because it says you
do!
If you aren't happy with the syntax supplied by the MySQL product and
SQL, you had better write your own RDBMS and your own SQL standard!
ok, I did not want to start a fight, sorry for saying what I said, it was
my ignorance speaking for me. I was just wondering -since my query is
just working- if that group-by is something that can alter the way the
results are sorted by (the entire query I didn't post ends with an
order by desc)
Jerry and I are not fighting - yet!

Aw, come on, Paul, put up your dukes! :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxx
==================

From: Captain Paralytic

Date: Friday, October 26, 2007

wrote in message:
wrote in message:
wrote in message:
> OH, well you had better argue that out with MySQL because it says you
> do!
> If you aren't happy with the syntax supplied by the MySQL product and
> SQL, you had better write your own RDBMS and your own SQL standard!
ok, I did not want to start a fight, sorry for saying what I said, it was
my ignorance speaking for me. I was just wondering -since my query is
just working- if that group-by is something that can alter the way the
results are sorted by (the entire query I didn't post ends with an
order by desc)
> Jerry and I are not fighting - yet!
Aw, come on, Paul, put up your dukes! :-)
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@xxxxxxxxxxx
==================

Yeah, you wanna come outside (of usenet) and say that }-()

Next Message: MySql server in my shareware application

Blogs related to query with COUNT (what am I doing wrong?)

http://bugs.mysql.com/bug.php'id=12744
I am doing this from Ruby, but does not seem to be a Ruby-specific issue. Any ideas' Danielss89 : hi, i get this error when trying to insert something into a table: Column count doesn't match value count at row 1 Danielss89 : i have no ...

can anyone help me get mysql server installed on Ubuntu dapper Ive ...
gar punctuation, have ya heard of it ? Isotopp, join ##php and ask in there instead. wrong nick. my english is not really good. oh sorry, gar join ##php not Isotopp. thats a mysql query or not i was thinking this was the good channel ...

Im developing a PHP web application and I have a query like this ...
and btw… what's "key distribution" in MySQL? hello i am currently doing a query like SELECT *,count(id),MAX(id) FROM channelpollvote WHERE channelpollid=2 GROUP BY vote; but is it possible for MAX to show the max results for the WHOLE ...

GRANT ALL PRIVILEGES ON hlstatsx* TO gameserver@reset4 IDENTIFIED ...
Can someone take a look at a query of mine and tell me why it takes so long to run (if it doesn't freeze my server) ? I'm sure I'm doing something wrong, I've never really used JOIN before and don't understand them that well. does mysql ...

Is there some way to tell mysql to not cache certain tables I have ...
what am I doing wrong here? select customers.first_name, count(invoices.*) from customers left join invoices on invoices.customer_id = customers.id group by customers.id;. depends on the error msg let me ask my crystal ball ...

i am ecommerce creating a custom Google map which has a method ...
PHP makes it very easy to query MySQL [and other databases]. yeah I understand what you mean by reusable functions I even used postgresql. I don’t currently have any ads, sorry. hi, i have seen some websites have thumbnails, ...