How do I COUNT rows of a GROUP BY query where a condition matches?
You can use conditional aggregation:
SELECT neighborhood, SUM(YEAR(NOW()) - p.birthyear) as under_18,
SUM(YEAR(NOW()) - p.birthyear BETWEEN 34 AND 42) as age_34_42
FROM persons p
GROUP BY neighborhood;
Getting the number of rows with a GROUP BY query
There is a nice solution in MySQL.
Add the keyword SQL_CALC_FOUND_ROWS right after the keyword SELECT :
SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff FROM t1, t2, t3
WHERE (associate t1,t2, and t3 with each other)
GROUP BY t3.id
LIMIT 10,20
After that, run another query with the function FOUND_ROWS() :
SELECT FOUND_ROWS();
It should return the number of rows without the LIMIT clause.
Checkout this page for more information : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
Count number of records returned by group by
You can do both in one query using the OVER clause on another COUNT
select
count(*) RecordsPerGroup,
COUNT(*) OVER () AS TotalRecords
from temptable
group by column_1, column_2, column_3, column_4
SELECT number of groups resulted from a GROUP BY query
SELECT count(*)
FROM (
SELECT 1 as dummy
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
) dt
No need to count rows in the sub-query, the result will be the same anyway.
How to get the total number of rows of a GROUP BY query?
The method I ended up using is very simple:
$query = 'SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele';
$nrows = $db->query("SELECT COUNT(1) FROM ($query) x")->fetchColumn();
Might not be the most efficient, but it seems to be foolproof, because it actually counts the original query's results.
How to use count and group by at the same select statement
This will do what you want (list of towns, with the number of users in each):
SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;
You can use most aggregate functions when using a GROUP BY
statement
(COUNT
, MAX
, COUNT DISTINCT
etc.)
Update:
You can declare a variable for the number of users and save the result there, and then SELECT
the value of the variable:
DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;
SELECT DISTINCT `town`, @numOfUsers FROM `user`;
How to count how many rows inside a group by group meets a certain criteria
I would suggest using CASE WHEN
(standard ISO SQL syntax) like in this example:
SELECT a.category,
SUM(CASE WHEN a.is_interesting = 1 THEN 1 END) AS conditional_count,
COUNT(*) group_count
FROM a
GROUP BY a.category
This will sum up values of 1 and null values (when the condition is false), which comes down to actually counting the records that meet the condition.
This will however return null when no records meet the conditions. If you want to have 0 in that case, you can either wrap the SUM
like this:
COALESCE(SUM(CASE WHEN a.is_interesting = 1 THEN 1 END), 0)
or, shorter, use COUNT
instead of SUM
:
COUNT(CASE WHEN a.is_interesting = 1 THEN 1 END)
For COUNT
it does not matter what value you put in the THEN
clause, as long as it is not null. It will count the instances where the expression is not null.
The addition of the ELSE 0
clause also generally returns 0 with SUM
:
SUM(CASE WHEN a.is_interesting = 1 THEN 1 ELSE 0 END)
There is however one boundary case where that SUM
will still return null. This is when there is no GROUP BY
clause and no records meet the WHERE
clause. For instance:
SELECT SUM(CASE WHEN 1 = 1 THEN 1 ELSE 0 END)
FROM a
WHERE 1 = 0
will return null, while the COUNT
or COALESCE
versions will still return 0.
Counting number of grouped rows in mysql
You need to do -
SELECT
COUNT(*)
FROM
(
SELECT
DISTINCT component
FROM
`multiple_sample_assay_abc`
WHERE
labref = 'NDQA201303001'
) AS DerivedTableAlias
You can also avoid subquery as suggested by @hims056 here
Related Topics
SQL Server: How to Get All Child Records Given a Parent Id in a Self Referencing Table
Export Database Schema into SQL File
Delete Data from All Tables in MySQL
Select Query by Pair of Fields Using an in Clause
SQL Most Recent Using Row_Number() Over Partition
Rename a Constraint in SQL Server
SQL Bulk Insert with Firstrow Parameter Skips the Following Line
MySQL Slow on First Query, Then Fast for Related Queries
Determine Row That Caused "Unexpected End of File" Error in Bulk Insert
Inline Blob/Binary Data Types in SQL/Jdbc
The Best Way to Use a Db Table as a Job Queue (A.K.A Batch Queue or Message Queue)
Store Select Query's Output in One Array in Postgres
Difference Between Int Primary Key and Integer Primary Key SQLite
Maintaining Referential Integrity - Good or Bad
How to Speed Up Row_Number in Oracle
How to Persist a Variable Across a Go
How to Run a SQL Plus Script in Powershell
"Microsoft.Ace.Oledb.12.0" Cannot Be Loaded In-Process on a 64-Bit SQL Server