Skip to content

Tinkering with groupings and counts in SQL

September 17, 2012

Today must seem the longest Monday ever, especially when tinkering a lot on MS SQL Queries and inserting them into tables.

Looking at my session tables, I wanted to look into counting the number of unique users from my session table with the creation time (  a count list of users per day that were created) , this may seem easy but if you are looking at two more tables in which to combine them, it is not as straightforward.

Looking at some old test data, I firstly used this to get the session data from 50 days ago. Obviously not the intrusive way of doing it, as you have to stick a value at the end. And its rather cumbersome.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000
FROM [portal].[dbo].[session]
WHERE StartTime >= GETDATE() – 51 AND StartTime <= GETDATE() – 50
ORDER By StartTime

Hence this is where grouping comes in.

I wrote this: –

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(a.UserID)
,DAY((s.CreationDate)) Day
,MONTH((s.CreationDate)) Month
,YEAR((s.CreationDate)) Year
FROM [portal].[dbo].[session] a, [portal].[dbo].[user] s
GROUP BY YEAR((s.CreationDate)),MONTH((s.CreationDate)), DAY((s.CreationDate)), a.UserID
ORDER BY YEAR((s.CreationDate)), MONTH((s.CreationDate)), DAY((s.CreationDate)), a.UserID

This gave some rather odd results, I have shown this as its an example of counting items with crossing over tables. Don’t do this, keep it simple and preferably use one table. Here it is doing a count of users both in the session table and user table combined based on the creation date.

I had reduced this down to this: –

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(s.UserID)
,DAY((s.CreationDate)) Day
,MONTH((s.CreationDate)) Month
,YEAR((s.CreationDate)) Year
FROM [portal].[dbo].[subscriber] s
GROUP BY YEAR((s.CreationDate)),MONTH((s.CreationDate)), DAY((s.CreationDate)), s.UserID
ORDER BY YEAR((s.CreationDate)), MONTH((s.CreationDate)), DAY((s.CreationDate)), s.UserID

But this groups by UserID, so it usually does a count of 1.

If you simple reduce this to: –

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(s.UserID)
,DAY((s.CreationDate)) Day
,MONTH((s.CreationDate)) Month
,YEAR((s.CreationDate)) Year
FROM [portal].[dbo].[user] s
GROUP BY YEAR((s.CreationDate)),MONTH((s.CreationDate)), DAY((s.CreationDate))
ORDER BY YEAR((s.CreationDate)), MONTH((s.CreationDate)), DAY((s.CreationDate))

This is correct! It does a count of users based on each day and on the day of the creation date (the date the subscriber was created).

This same format can be used for calculating the number of usersin the sessions table!

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(DISTINCT a.UserID) count
,DAY(StartTime) Day
,MONTH(StartTime) Month
,YEAR(StartTime) Year

FROM [portal].[dbo].[session] a
GROUP BY YEAR(StartTime),MONTH(StartTime), DAY(StartTime)
ORDER BY YEAR(StartTime), MONTH(StartTime), DAY(StartTime)

A heed of warning from what I have found out not only today, but over the last few months.

First thing you realise when you are testing SQL queries is to make sure you run queries on a test database table before placing them in a SQL job on the live server.

Secondly, when you are combining two or more tables which I had found out, make sure that you don’t mix the ambiguous (same) column names on the select query, they do if mixed up give different results.

Thirdly, Microsoft can give SQL query errors randomly, if you don’t get it to compile the first time on a query run, try refreshing the query or a part of the syntax and try again. It might just work!

 

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: