Solved

Mssql SQL query

Posted on 2016-11-29
14
68 Views
Last Modified: 2016-11-29
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
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +3
14 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
ID: 41905316
Use the YEAR and MONTH function:
select [InsertedDate],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 [InsertedDate]],year(InsertedDate), month(InsertedDate)  
 Order by [InsertedDate] asc 

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905321
Try..

SELECT * , COUNT(*) OVER (PARTITION BY YEAR([InsertedDate]),MONTH([InsertedDate]))  EventMonthCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 

Open in new window


If you want sum of any column use below- SUM(YourColumnName)

SELECT * , SUM(YourColumnName) OVER (PARTITION BY YEAR([InsertedDate]),MONTH([InsertedDate]))  EventMonthCount
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 

Open in new window


Hope it helps !!
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41905328
for Vitor's comment, you no need to select [InsertedDate] ... and  group by [InsertedDate],

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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 325 total points
ID: 41905331
Or you can use this..

select YEAR([InsertedDate]) YearInsrt ,MONTH([InsertedDate]) MonthInst , COUNT(*) CountByYearMonth
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
group by YEAR([InsertedDate]),MONTH([InsertedDate])
Order by YEAR([InsertedDate]),MONTH([InsertedDate])

Open in new window


Hope it helps !!
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905334
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.
0
 

Author Comment

by:motioneye
ID: 41905352
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
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905356
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' 

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905360
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' 

Open in new window

0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 325 total points
ID: 41905364
Or...use can use from below--

1.........

select CONCAT(  DATEFROMPARTS( YEAR([InsertedDate]) ,MONTH([InsertedDate]) , '01' ) , ' - ' ,
EOMONTH(DATEFROMPARTS( YEAR([InsertedDate]) ,MONTH([InsertedDate]) , '01' )) ) InsertedDate
, COUNT(*) CountByYearMonth
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
group by YEAR([InsertedDate]),MONTH([InsertedDate])
Order by YEAR([InsertedDate]),MONTH([InsertedDate])

Open in new window


2..............

select DATEFROMPARTS( YEAR([InsertedDate]) ,MONTH([InsertedDate]) , '01' ) InsertedDate
, COUNT(*) CountByYearMonth
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
group by YEAR([InsertedDate]),MONTH([InsertedDate])
Order by YEAR([InsertedDate]),MONTH([InsertedDate])

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905371
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
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 325 total points
ID: 41905372
If you are using SQL 2008..

1.........

select CAST(  CAST(CAST(YEAR([InsertedDate]) AS VARCHAR(4)) + '/' + LEFT('0'+CAST(MONTH([InsertedDate]) AS VARCHAR(2)),2) + '/' + '01' AS DATE) AS VARCHAR(15)) , ' - ' ,
CAST( EOMONTH(CAST(CAST(YEAR([InsertedDate]) AS VARCHAR(4)) + '/' + LEFT('0'+CAST(MONTH([InsertedDate]) AS VARCHAR(2)),2) + '/' + '01' AS DATE) ) AS VARCHAR(15))
InsertedDate
, COUNT(*) CountByYearMonth
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
group by YEAR([InsertedDate]),MONTH([InsertedDate])
Order by YEAR([InsertedDate]),MONTH([InsertedDate])

Open in new window


The output will looks like.. <<Month will increase , day will be the first day of the month and the last day of the month everytime, I think thats what you wanted.>>

InsertedDate                        EventCount
2016-01-01 - 2016-01-31       4216
2016-02-01  - 2016-02-29      1925
2016-03-01 - 2016-03-30       1854
2016-04-01 - 2016-03-31       4606

2..............

select CAST(CAST(YEAR([InsertedDate]) AS VARCHAR(4)) + '/' + LEFT('0'+CAST(MONTH([InsertedDate]) AS VARCHAR(2)),2) + '/' + '01' AS DATE) [InsertedDate]
, COUNT(*) CountByYearMonth
from ArchiveLoginsLogs
where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
group by YEAR([InsertedDate]),MONTH([InsertedDate])
Order by YEAR([InsertedDate]),MONTH([InsertedDate])

Open in new window


The output will looks like.. <<Month will increase , day will be the first day of the month everytime, I think thats what you wanted.>>

InsertedDate      EventCount
 2016-01-01      4216
 2016-02-01      1925
 2016-03-01      1854
 2016-04-01      4606

Hope it helps !!
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 41905394
To include a monthly total you could use "group by rollup" although the labels for the subtotals gets a bit tricky in my opinion
try this:
select
          coalesce(InsertedDate, left(lag(InsertedDate,1) over(order by (select 1)),7),'total') InsertedDate
        , event_count
from (
        select
              convert(varchar(10),InsertedDate,120) InsertedDate
            , count(*) event_count
        from ArchiveLoginsLogs
        where [InsertedDate] >='2010-01-01' and [InsertedDate] <'2016-11-29' 
        group by rollup ( datepart(mm,InsertedDate), InsertedDate ) 
    ) g
;

Open in new window

a result would look something like this:
+----+--------------+-------------+
|    | InsertedDate | event_count |
+----+--------------+-------------+
|  1 | 2016-01-01   |           2 |
|  2 | 2016-01-11   |           1 |
|  3 | 2016-01-21   |           1 |
|  4 | 2016-01      |           4 |
|  5 | 2016-02-02   |           1 |
|  6 | 2016-02-12   |           2 |
|  7 | 2016-02-22   |           1 |
|  8 | 2016-02      |           4 |
|  9 | 2016-03-03   |           2 |
| 10 | 2016-03-13   |           1 |
| 11 | 2016-03-23   |           1 |
| 12 | 2016-03      |           4 |
| 13 | 2016-04-04   |           1 |
| 14 | 2016-04-14   |           1 |
| 15 | 2016-04-24   |           1 |
| 16 | 2016-04      |           3 |
| 17 | total        |          15 |
+----+--------------+-------------+

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41905961
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],
...
0
 

Author Closing Comment

by:motioneye
ID: 41906014
Thanks guys,I managed it with your guys help
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question