SQL Result using COUNT()

[NICON]

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

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.

[kicken]

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

 

[NICON]

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.

[NICON]

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.

[kicken]

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.

[NICON]

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.

[Barand]

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.

[NICON]

Okay that makes sense for sure.

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:

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:

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.

[Barand]

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 June 22, 2020 by Barand

[NICON]

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 June 22, 2020 by NICON