[SOLVED] COUNT()

[jaymc]

When I use COUNT() in my query I get an error

 

Here is the query

 

SELECT SQL_CALC_FOUND_ROWS `viewer`, `time`, `views`, SUM(views) FROM `viewedpro`

 

syntax error?

 

It works fine if I do

 

SELECT SUM(views) FROM `viewedpro`

[akitchin]

as a warning, i have no idea what the SQL_CALC_FOUND_ROWS is there for.

 

i assume you receive a "cannot use aggregate functions without the GROUP BY operator" error?  this is because you're trying to run an aggregate function while selecting individual values.

 

if you're specifying a function that uses multiple rows in its calculation (aggregate, such as SUM()), and telling it to grab values from an individual row as well, how does it know which individual row to grab?  assuming the SUM(views) is for a certain "group", you need to group by that column so that it knows that for a given set of the same column value, it should grab the SUM(views) for that set.

 

it would be helpful to see some database structure and hear what you're actually trying to do.

[jaymc]

I have a WHERE `name` = 'jaymc' in my query, I forgot to add that in..

 

Surely thats the same as a group by?

[akitchin]

negatory, they are distinct features, if somewhat subtle.  WHERE will specify what rows to restrict the aggregate function to DURING its calculation, while GROUP BY will tell restrict the rows BEFORE running the calculation.  see this article for more info:

 

http://www.databasejournal.com/features/mysql/article.php/3469351

 

for example, while SUM()ming a dummy column of which there are many for one username, specifying GROUP BY username and WHERE rank=4 will say grab one SUM() of all columns that have the same username, including only those rows whose rank is 4.

[jaymc]

So how can I use the SUM function in this query

 

$queryj = "SELECT SQL_CALC_FOUND_ROWS `viewer`, `time`, `views`, SUM(views) FROM `viewedpro` WHERE `username` = '$_GET[user]' GROUP BY `user` ORDER BY `time` DESC LIMIT 0,5";

 

[akitchin]

if you want to grab all of the viewers and the sum of their views for a single user, you would group by viewer (because the SUM()s are related to that viewer themselves), and select where the username = 'user'.

 

SELECT `viewer`, SUM(views) AS totalviews FROM `viewedpro` WHERE `username` = '$_GET[user]' GROUP BY `viewer` ORDER BY `time` DESC

 

this will grab each unique viewer of $_GET[user], along with the SUM() of their views.  perhaps i've mistaken what you're trying to grab - your previous query seems pretty diluted.

[jaymc]

Still nStill not working

 

 

$queryj = "SELECT SQL_CALC_FOUND_ROWS `viewer`, `time`, `views`, SUM(views) FROM `viewedpro` WHERE `username` = '$_GET[user]' GROUP BY `viewer` ORDER BY `time` DESC LIMIT 0,5";

 

$doqueryj = mysql_query($queryj);

$tts = number_format($doqueryj['SUM(views)']);

 

Not giving an error but the output is 0

[jaymc]

This works fine though

 

$queryj = "SELECT SUM(views) FROM viewedpro WHERE username = '$_GET[user]' LIMIT 0,1";

 

But i cant use that because its a new query altogether!

[akitchin]

what exactly are you trying to calculate?  it's not very clear from the queries you've used so far.

[jaymc]

I just want to add all the values for the `views` together for one user to give me the total

 

Because Im already running a query which queries this table, Im trying to put a SUM in there rather than in another query which is what I have at the moment

[akitchin]

if you're already pulling the information from the rows, you might as well tally the sum of views manually from the information pulled.  you only save time/resources with aggregate functions when you weren't necessarily going to pull the data that the function operates on.

 

array_sum() will help you out here.

[jaymc]

I cant do that, because I have a limit 0,5 on the initial query

 

Where as there maybe 5,000 rows for that user..

[akitchin]

so are you trying to show the 5 most recent viewers, along with the total sum?  if so, then consolidation of the two queries won't gain you any ground here.  using SQL_CALC_FOUND_ROWS, as far as i know, won't help you much as you have to run a second query to grab those rows found anyway.  there is a bug regarding this feature as well, see the MySQL manual SELECT syntax page for more info.

[jaymc]

I'll just keep it in seperate queries then, I dont suppose it will do that much harm

 

Cheers for feedback guys its been interesting!