Jump to content
NICON

SQL Result using COUNT()

Recommended Posts

SELECT COUNT(id) c, id, owner FROM cities WHERE owner IN ('21', '37') AND available = 0

image.png.1d094ea20f6d4d33e76b53e74cf87f45.png

So the result for the count is 2 as shown but the result is only showing the first of the 2 rows. I have looked far and wide and cant seem to pin down why my result is incorrect. Thanks in advance for your help.

Share this post


Link to post
Share on other sites

Any columns that you are selecting but not COUNT()'ing need to be part of a GROUP BY clause.

SELECT 
	COUNT(id) c, 
    id, 
    owner 
FROM cities 
WHERE 
	owner IN ('21', '37') 
	AND available = 0
GROUP BY
	id, owner

 

Share this post


Link to post
Share on other sites

Correct me if I am wrong but by grouping wont that mean that my count will only be one unless a duplicate entry is found. In this case my query will only result in 0, 1 or 2 and will never be more than that. So now the query will give me both results as I wanted but the count is also only one even though 2 results are found.

Share this post


Link to post
Share on other sites

To explain better I am searching this table for results. There may be 100 rows in this table and never will there be more than 2 results. Some cases there will be 1 or 2 results. If there are only 1 result I want it to do this and if there are 2 results and the current result is not what I am looking for I have it loop again until I get that result. The issue I was having with the old code was that it would only loop once because it was only fetching the single row despite the fact that the count was in fact picking up 2 rows. The count has always been correct it was just not fetching the result properly.

Share this post


Link to post
Share on other sites

I have no idea still what you're trying to do, but it sounds like you just don't want to use COUNT() at all.  Just select id, owner and then loop over the returned rows in your code.

Count will tell you how many rows match your query, but you cannot select it along with other columns and get meaningful results unless you use GROUP BY to divide the results into different sets.  Then COUNT() will tell you how many rows exist in each of those sets.

Share this post


Link to post
Share on other sites

I want COUNT() to be the total number of sets and the query to return the results of id and owner for each of the result sets.

Share this post


Link to post
Share on other sites

Perhaps if you gave us some sample input data and then tell us what your desired output is from that data. Then we might understand what you mean.

Share this post


Link to post
Share on other sites

Okay that makes sense for sure.

image.png.b7051a9359d3537427cc8a94008c4619.png

Thats the table being queried.

When I use the following:

SELECT COUNT(id) c, id, owner FROM cities WHERE owner IN ('21', '37') AND available = 0

My results are:

image.png.5eb3eaf845a20c70993976004bdf622a.png

When I use this:

SELECT 
	COUNT(id) c, 
    id, 
    owner 
FROM cities 
WHERE 
	owner IN ('21', '37') 
	AND available = 0
GROUP BY
	id, owner

My results are:

image.png.4981875d53242a7483bd819510b3f894.png

I want my count in this case to be 2 (my count will always be 0, 1, or 2 depending on how many rows are populated with the 21 or 37 value). These values change based on the person using this page and the owner being checked.

I also want the 2 results for id and owner that I get when using the GROUP BY.

So count here should be 2 and I should get output for the id and owner for both results.

Hope that clears it up.

Share this post


Link to post
Share on other sites
Posted (edited)
SELECT total as c 
     , id
     , owner
FROM cities
     CROSS JOIN
          (    
			SELECT COUNT(*) as total
			FROM cities
			WHERE owner IN (21, 37)
          ) tot        
WHERE owner IN (21, 37);

What determines that those owner ids being searched for should be 21 and 37? Are they the result of another query (to find those in a "set")?

Edited by Barand

Share this post


Link to post
Share on other sites
Posted (edited)

Those 2 id's are determined by a global variable $user->id and a defined variable earlier in the script $g defined in a class. The $g is defined by $_POST actually. So the user's id is taken from who is submitting the form and the $_POST variable is defined through the class using the id when the user hits submit.

Edited by NICON

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.