motioneye
asked on
Mssql SQL query
Guys,
I have a query below that works to get for me daily count of daily data, but how about if I want to get this query only to give me sums of monthly data ? how the query data looks like ?
select [InsertedDate],count ([InsertedDate]) as EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate]
<'2016-11-29'
group by [InsertedDate]
Order by [InsertedDate] asc
I have a query below that works to get for me daily count of daily data, but how about if I want to get this query only to give me sums of monthly data ? how the query data looks like ?
select [InsertedDate],count ([InsertedDate]) as EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate]
<'2016-11-29'
group by [InsertedDate]
Order by [InsertedDate] asc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for Vitor's comment, you no need to select [InsertedDate] ... and group by [InsertedDate],
hence:
hence:
select year(InsertedDate) as 'Year', month(InsertedDate) as 'Month', count ([InsertedDate]) as EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29'
group by year(InsertedDate), month(InsertedDate)
Order by 1,2
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for Vitor's comment, you no need to select [InsertedDate] ... and group by [InsertedDate],True. I just copied the code and forgot to update that part. Nice catch, Ryan.
ASKER
Guys,
Both query did not return me the data I want, the query below provide me with counts of data daily, but how do I sums this EventCount so that I can present the count monthly ? so the output that I'm looking for shoud be like thsi .
InsertedDate EventCount
2016-01-01 4216
2016-02-02 1925
2016-03-03 1854
2016-04-04 4606
Or can be like this
InsertedDate EventCount
2016-01-01 - 2016-01-31 4216
2016-02-02 - 2016-02-29 1925
2016-03-03 - 2016-03-30 1854
2016-04-04 - 2016-03-31 4606
select [InsertedDate],count ([InsertedDate]) as EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate]
<'2016-11-29'
group by [InsertedDate]
Order by [InsertedDate] asc
short output from the query
InsertedDate EventCount
2016-01-01 4216
2016-01-02 1925
2016-01-03 1854
2016-01-04 4606
Both query did not return me the data I want, the query below provide me with counts of data daily, but how do I sums this EventCount so that I can present the count monthly ? so the output that I'm looking for shoud be like thsi .
InsertedDate EventCount
2016-01-01 4216
2016-02-02 1925
2016-03-03 1854
2016-04-04 4606
Or can be like this
InsertedDate EventCount
2016-01-01 - 2016-01-31 4216
2016-02-02 - 2016-02-29 1925
2016-03-03 - 2016-03-30 1854
2016-04-04 - 2016-03-31 4606
select [InsertedDate],count ([InsertedDate]) as EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate]
<'2016-11-29'
group by [InsertedDate]
Order by [InsertedDate] asc
short output from the query
InsertedDate EventCount
2016-01-01 4216
2016-01-02 1925
2016-01-03 1854
2016-01-04 4606
Try..
SELECT DISTINCT DATEFROMPARTS(YEAR([InsertedDate]),MONTH([InsertedDate]),'01') InsertedDate , COUNT(*) OVER (PARTITION BY YEAR([InsertedDate]),MONTH([InsertedDate])) EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29'
Or this...
SELECT DISTINCT CONCAT( DATEFROMPARTS(YEAR([InsertedDate]),MONTH([InsertedDate]),'01') , ' - ' ,
EOMONTH(DATEFROMPARTS(YEAR([InsertedDate]),MONTH([InsertedDate]),'01')) )InsertedDate
,COUNT(*) OVER (PARTITION BY YEAR([InsertedDate]),MONTH([InsertedDate])) EventCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The periods shouldn't start all at day one? Why Feb starts at day two and March at day three and so on?
InsertedDate EventCount
2016-01-01 - 2016-01-31 4216
2016-02-02 - 2016-02-29 1925
2016-03-03 - 2016-03-30 1854
2016-04-04 - 2016-03-31 4606
Also, if you want a SUM why the above example isn't a SUM but the values from January days?
InsertedDate EventCount
2016-01-01 4216
2016-01-02 1925
2016-01-03 1854
2016-01-04 4606
InsertedDate EventCount
2016-01-01 - 2016-01-31 4216
2016-02-02 - 2016-02-29 1925
2016-03-03 - 2016-03-30 1854
2016-04-04 - 2016-03-31 4606
Also, if you want a SUM why the above example isn't a SUM but the values from January days?
InsertedDate EventCount
2016-01-01 4216
2016-01-02 1925
2016-01-03 1854
2016-01-04 4606
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [InsertedDate]), 0) AS [InsertedMonth],
COUNT(*) AS EventCount
FROM ArchiveLoginsLogs
WHERE [InsertedDate] >='2010-01-01' AND
[InsertedDate] < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [InsertedDate]), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [InsertedDate]), 0)
Don't use string conversion in any way (unless absolutely unavoidable), because it will vastly slow down (and complicate) the query.
The general format above could be used for yearly totals also, for example:
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, [InsertedDate]), 0) AS [InsertedYear],
...
Thus, this technique is not only very efficient but it's also consistent for different date granularities. For example, if you had a full datetime and wanted hourly totals:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, [InsertedDate]), 0) AS [InsertedHour],
...
COUNT(*) AS EventCount
FROM ArchiveLoginsLogs
WHERE [InsertedDate] >='2010-01-01' AND
[InsertedDate] < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [InsertedDate]), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [InsertedDate]), 0)
Don't use string conversion in any way (unless absolutely unavoidable), because it will vastly slow down (and complicate) the query.
The general format above could be used for yearly totals also, for example:
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, [InsertedDate]), 0) AS [InsertedYear],
...
Thus, this technique is not only very efficient but it's also consistent for different date granularities. For example, if you had a full datetime and wanted hourly totals:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, [InsertedDate]), 0) AS [InsertedHour],
...
ASKER
Thanks guys,I managed it with your guys help
Open in new window
If you want sum of any column use below- SUM(YourColumnName)
Open in new window
Hope it helps !!