query with COUNT (what am I doing wrong?)

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 have 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!

On 25 Oct, 12:00, panchettone <pan_che_ttone@non_morde.bau> wrote:
> 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 have 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

>
> 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
]

On 25 Oct, 12:27, panchettone <pan_che_ttone@non_morde.bau> wrote:
> > 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

Captain Paralytic ha scritto:

>
> Well that's basic GROUP BY syntax!



well, the fact is I don't need any group by

On 25 Oct, 14:12, panchettone <pan_che_ttone@non_morde.bau> wrote:
> Captain Paralytic ha scritto:
>
>
>
> > Well that's basic GROUP BY syntax!
>
> well, the fact is I don't need any group by

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

If you are not happy with the syntax supplied by the MySQL product and
SQL, you had better write your own RDBMS and your own SQL standard!

Captain Paralytic wrote:
> On 25 Oct, 14:12, panchettone <pan_che_ttone@non_morde.bau> wrote:
>> Captain Paralytic ha scritto:
>>
>>
>>
>>> Well that's basic GROUP BY syntax!
>> well, the fact is I don't need any group by
>
> OH, well you had better argue that out with MySQL because it says you
> do!
>
> If you are not 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 hasn't enforced it that much
in the past.

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

On 25 Oct, 14:41, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Captain Paralytic wrote:
> > On 25 Oct, 14:12, panchettone <pan_che_ttone@non_morde.bau> wrote:
> >> Captain Paralytic ha scritto:
>
> >>> Well that's basic GROUP BY syntax!
> >> well, the fact is I don't need any group by
>
> > OH, well you had better argue that out with MySQL because it says you
> > do!
>
> > If you are not 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!"

>
> OH, well you had better argue that out with MySQL because it says you
> do!
>
> If you are not 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 didn't 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 did not post ends with an
order by desc)

> 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?