Menu
I have a table like the following
ID created sent type
-----------------------------------------------------
0001463583000051783 31-JUL-12 1 270
0081289563000051788 01-AUG-12 1 270
0081289563000051792 01-AUG-12 1 270
0081289563000051791 01-AUG-12 1 270
0081289563000051806 01-AUG-12 1 270
0001421999000051824 06-AUG-12 1 270
0001421999000051826 06-AUG-12 1 270
0001464485000051828 06-AUG-12 1 270
0082162128000051862 09-AUG-12 2 278
0082162128000051861 09-AUG-12 2 278
0022409222082910259 09-AUG-12 3 278
I would like to have the following for output
created Count
---------------------
31-JUL-12 1
01-AUG-12 4
06-AUG-12 3
09-AUG-12 3
How hard would it be to accomplish this using SQL Developer on Oracle 10g
I have tried several queries to generate such a table and in the end it does not group the count by date just gives me a '1' for the count when we average 5000-10000 transactions daily. Im probably over complicating it. But i would like something simple where i can pull the amount of transactions on a daily basis within a date range.
what is happening currently when i run my queries is
created Count
---------------------
31-JUL-12 1
01-AUG-12 1
01-AUG-12 1
01-AUG-12 1
01-AUG-12 1
06-AUG-12 1
06-AUG-12 1
06-AUG-12 1
09-AUG-12 1
09-AUG-12 1
09-AUG-12 1
788
If you specify expr, then COUNT returns the number of rows where expr is not null. ... If you specify the asterisk (*), then this function returns all rows... In other words, COUNT(fecha_devolucion) counts non-NULL values of that column. COUNT(*) counts the total number of rows, regardless of the values.
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.
If you only want a total count of sales every month, then you can use COUNT function instead. mysql> select year(order_date),month(order_date),sum(sale) from sales WHERE condition group by year(order_date),month(order_date) order by year(order_date),month(order_date);
select table_name, count(*) from all_tab_columns where owner = 'SOME_USER' group by table_name order by table_name; More details about the system catalogs can be found in the manual: ALL_TAB_COLUMNS.
I managed to get this results with this query:
select trunc(created), count(*)
from table1
group by trunc(created)
Note the trunc function, even if you don't display it, the DATE datatype holds the time as well
Here is a fiddle
77
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
