CODEWITHSUNDEEP
sqlamazon-web-servicesstatisticsamazon-redshift
Viv
Viv

Reputation: 1584

How to calculate the median count for year / week/ month/day?

I have some data in table which contains date as one column and count(*).

But I have requirement to draw a chart of median or mean count for that year/month/week/day and the error bar represents the spread of the data

SELECT DATE_PART(year,l_date) as year, (count(*))
from am_history
group by year;

which gives the year and count of rows per year. How to calculate the median for this count(*) and error (standard deviation)? I understand if for year i find out the query, the rest for month, week etc can be done easily.

I am new to calculate statistics,Please help

Upvotes: 0

Views: 500

Answers (1)

Hemang A
Hemang A

Reputation: 1012

Please try this.

SELECT DATEPART(year,l_date) as [year], count(l_date) as counts 
from am_history
group by DATEPART(year,l_date);

Example:---

Declare @tbl Table(
id INT ,
[group]  Varchar(50),
value varchar(50),
Dat DATETIME
)

INSERT INTO @tbl VALUES(1,'1.1.03.01.00.00',NULL,GETUTCDATE()-100)
INSERT INTO @tbl VALUES(2,'1.1.03.01.01.00',NULL,GETUTCDATE() + 7)
INSERT INTO @tbl VALUES(3,'1.1.03.01.02.00',NULL,GETUTCDATE()+10)

SELECT DATEPART(year,Dat)As years,count(Dat) as counts  FROM @tbl  group by DATEPART(year,Dat)

Upvotes: 0

Related Questions