Menu
I have the following structure
ID DATE(DATETIME) TID
1 2012-04-01 23:23:23 8882
I'm trying to count the amount of rows and group them by each day of the month that matches TID = 8882
Thanks
902
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
SELECT DISTINCT ColumnName FROM TableName; Using the COUNT() function with the GROUP BY clause, then the query will produce the number of values as the count for each subgroup created based on the table column values or expressions.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column. we can use the following command to create a database called geeks.
You can group using the DAY function:
SELECT DAY(Date), COUNT(*)
FROM table
WHERE TID = 8882
GROUP BY DAY(Date)
Not sure exactly what you mean by day of the month -- do you want to group the 1st of Feb with the 1st of March? Or do you just mean date? Assuming the latter, how about this:
SELECT DATE(date) as d,count(ID) from TABLENAME where TID=8882 GROUP by d;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
