Reputation: 5094
I have in my cassandra database these data:
+----+----------+-----+-----------+---------+----------+
| ID | NAME | hour_date | value |
+----+----------+-----+-----------+---------+----------+
| 1 | Ramesh | 2017-04-16 05:00:00+0000 | 2000.00 |
| 2 | Ramesh | 2017-04-16 08:00:00+0000 | 1500.00 |
| 3 | Ramesh | 2017-04-16 11:00:00+0000 | 2000.00 |
| 4 | Komal | 2017-04-22 05:00:00+0000 | 6500.00 |
| 5 | Komal | 2017-04-16 09:00:00+0000 | 8500.00 |
| 6 | Komal | 2017-04-22 17:00:00+0000 | 4500.00 |
| 7 | Komal | 2017-04-22 05:00:00+0000 | 10000.00 |
+----+----------+-----+-----------+---------+----------+
By using SQL queries, I'm tring to calculate how many values each name has in a specific date. Here is the example of output I'm looking for:
Output:
Ramesh:
2017-04-16 : 3 values
Komal:
2017-04-16 : 1 value
2017-04-22 : 3 values
I tried doing this:
SELECT hour_date,name,count(value)
FROM table WHERE
(
SELECT hour_date from table
WHERE hour_date = '2017-04-16 05:00:00+0000'
)
But that does not work. How can I query the table to give for each name and for each hour_date how many values do I have? I can't figure this out.
I use pyspark to make the sql queries with the cassandra connector. Any suggestions would be very appreciated.
Upvotes: 1
Views: 469
Reputation: 5926
You need to group by the values you want to count on, which in your case are the user and the date part of your datetime
select name, toDate(hour_date), count(distinct id)
from table
group by name, toDate(hour_date)
order by name, toDate(hour_date)
The toDate()
function will only keep the date part, excluding the hour, minutes and seconds.
Note also that the count argument is distinct id
, because your version would count two rows with the same value as one.
Upvotes: 1
Reputation: 5155
You can use below query
SELECT trunc(hour_date),name,count(value)
FROM table group by trunc(hour_date), name;
Trunc function bypasses the timestamp and you can get the count of data of particular date with respect to the names. It you want you can also order by date and name
order by trunc(hour_date), name
Upvotes: 2
Reputation: 5060
I think you can use (in MYSQL):
SELECT name,DATE(hour_date) AS date_only, count(value) as values
FROM table
group by name, DATE(hour_date)
order by name, DATE(hour_date)
Upvotes: 1
Reputation: 1245
You missed group by for aggregated function count. Try this:-
SELECT name,hour_date,count(value) as values
FROM table
group by name, hour_date
order by name, hour_date
Upvotes: 1