Souad
Souad

Reputation: 5094

SQL query to count how many values according to other fields

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

Answers (4)

Stefano Zanini
Stefano Zanini

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

Jim Macaulay
Jim Macaulay

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

etsa
etsa

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

India.Rocket
India.Rocket

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

Related Questions