October 4, 2014 at 3:21 pm
Hello,
I am very new and using PostgreSQL. I am asked to write a query that gives me new user counts that joined during a specific year # and month # in separate columns. I was also asked to write a query for year # the user joined and the month# from my_table with the join date stored in create_date column (timestamp with timezone). Some users don't have a join date. Please help!
Thanks a bunch!
October 5, 2014 at 2:29 pm
This is a forum for Microsoft SQL Server, so I don't know if it's compatible. Also you should provide sample code (create table and insert sample rows) and desired output to make excactly clear what you want.
For as far as I've understood your question, this could point you in the right direction:
create table #my_table (name varchar(50), create_date datetime)
insert into #my_table values('Frank', '20131010')
, ('Peter', '20131013')
, ('Josh', '20130909')
, ('Ben', '20131020')
, ('Carl', '20131110')
, ('Jason', '20131115')
, ('Bart', '20131201')
, ('Abe', '20140101')
, ('Kevin', '20140120')
, ('Mike', NULL)
, ('Ken', '20140202')
select
YEAR(create_date) as year
, DATENAME(month, create_date) as month
, count(MONTH(create_date)) as counts
FROM #my_table
group by
year(create_date)
, MONTH(create_date)
, DATENAME(month, create_date)
order by YEAR(create_date), MONTH(create_date)
drop table #my_table
October 5, 2014 at 3:09 pm
Thanks!
That definitely points me in the right direction. The syntax a is bit different from SQL but now I know how to go about it. Thank you very much.
Kalyani
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply