CODEWITHSUNDEEP
sqlhadoophivehiveql
eluth
eluth

Reputation: 79

Hive - Count the number of records for each day in a DB

I have a hive table that has millions of records and each row is a unique record that has a date - example below:

name, date, item
bob, jan 1, brush
jim, jan 1, computer
mary, jan 1, mouse
bill, jan 2, mop
james, jan 3, desk

I want the output to just show me how many records there are per day. I tried:

select distinct(date), count(distinct(date)) distinct for date
from DB

and

select distinct(date) from DB group by count(distinct(date))

I realize I can't just do count(distinct(date)) because that just gives me the number of total distinct dates. I want the output to be:

date, count
jan 1, 3
jan 2, 1
jan 3, 1

Thanks in advance,

Upvotes: 1

Views: 4969

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

This is a basic group by query:

select date, count(*)
from DB
group by date;

I would suggest that you spend a bit of time studying the basics of SQL queries. There are many online resources.

Upvotes: 3

Related Questions