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
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 on GROUP BY and also on total number of results
You can use an analytic/window function count without any partition:
SELECT users.id as user_id, COUNT(*) as houses_sold,
COUNT(*) OVER() as total_count -- count of rows returned by query
FROM users
JOIN users_with_house_permissions hp ON hp.user_id = users.id
LEFT JOIN houses on houses.user_id = users.id AND houses.sold_at IS NOT NULL
GROUP BY users.id
It works like any other analytic function; it counts over a partition, but if there is no partition specified, then it counts over the entire dataset. In this case the countover is done after the grouping is done, so while count(*)
counts the number of items in the group, count(*) over()
counts the number of groups in the data set
Someone else posted a sum(count(*)) over()
which is the effective equivalent of counting the rows before they are grouped. If you had a data set of "who sold the house" and it went thus:
john
john
john
mary
4 houses have been sold, john sold 3, mary sold 1. There are 2 sales reps working for the agency.
COUNT(*) FROM ... GROUP BY name
gives the "john sold 3, mary sold 1" and results in a dataset of:
john, 3
mary, 1
If we were to SUM that count, we'd have 4, i.e. 3+1. This is effectively the count of houses before the grouping was done. SUM(COUNT(*)) OVER()
is hence the count of rows we had before we did the group. It's important to bear in mind that the COUNT(*) belongs to the GROUP BY and will become an integer count that is then later SUMmed by the SUM OVER. It would probably be easier to see if we used a subquery:
SELECT name, the_count, SUM(the_count) OVER()
FROM (SELECT name, count(*) as the_count FROM sales GROUP BY name) subquery
But because analytics are calculated after grouping is done, there isn't really any need to present it like this; the db would do this in the same way as it would do:
SELECT name, count(*), sum(count(*)) over() FROM sales GROUP BY name
Thus we get to the point where you appreciate that analytics are applied after a grouping is performed, it means that where COUNT(*) OVER()
is a count of the number of rows in the dataset after the grouping operation is finished. The grouping produced john,3|mary,1
so COUNT(*) OVER() this produces 2 - the number of rows in the dataset
The documentation, if you want to read more, is titled "Windows Functions" and can be found somewhere like here: https://www.postgresql.org/docs/9.1/tutorial-window.html
This is for PG 9.1; remember to change the view to your specific version of PG
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.
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
SQL how to count total rows for each customer with group by?
Use COUNT(*)
to get the total number of movie rentals, and group it by city.
SELECT city.city, COUNT(*) AS total_rentals
FROM rental AS r
INNER JOIN customer AS cust on r.customer_id = cust.customer_id
INNER JOIN address AS a on cust.address_id = a.address_id
INNER JOIN city ON city.city_id = a.city_id
GROUP BY city.city_id
ORDER BY total_rentals DESC
LIMIT 1
If you also want to know the number of customers in that city, you can add COUNT(DISTINCT cust.customer_id)
to the SELECT
list.
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 total rows and grouping by a column in mysql
First you need group by to get the calculated columns
SELECT Customer, COUNT(*) as total_count, SUM(duration) as total_duration
FROM yourTable
GROUP BY Customer
and
SELECT Customer, Error, COUNT(*) as error_count
FROM yourTable
GROUP BY Customer, Error
Then join back to you table
SELECT t1.Customer,
t2.total_count,
t1.error,
t3.error_count,
t2.total_duration
FROM yourTable as t1
JOIN (
SELECT Customer, COUNT(*) as total_count, SUM(duration) as total_duration
FROM yourTable
GROUP BY Customer
) as t2
ON t1.Customer = t2.Customer
JOIN (
SELECT Customer, Error, COUNT(*) as error_count
FROM yourTable
GROUP BY Customer, Error
) as t3
ON t1.Customer = t3.Customer
AND t1.Error = t3.Error
GROUP BY t1.Customer
, t1.Error
Related Topics
Destroy PHP Session on Closing
Mcrypt_Encrypt to Openssl_Encrypt, and Openssl_Zero_Padding Problems
MVC (Laravel) Where to Add Logic
Laravel 5 Change Public_Path()
Getting ’ Instead of an Apostrophe(') in PHP
PHP: Cookie Domain/Subdomain Control
Custom Pagination View in Laravel 5
Laravel: How to Change the Default Auth Password Field Name
PHP Script Not Working in HTML File
Warning: Session_Start() Failed: No Such File or Directory
Fatal Error: Maximum Execution Time of 300 Seconds Exceeded