Wednesday, September 5, 2012

Tech: SQL GROUP BY with SUM

Tech: SQL GROUP BY with SUM

sql_serverFrom time to time I plan on posting things I have to deal with in the real world.  Often I find articles that are helpful on the web, but other times I have to piece the knowledge I am looking for from several places and combine that information for my final answer.

This will be a short article dealing with a very simple application.  Using the SUM function to return results based on a group of items. So here goes:

Table with Multiple Entries per Date

First I queried a table I had created by importing a spreadsheet into a SQL tabled named "Tracking".  I wanted to ensure that the data contained in the spreadsheet imported properly into the Table and the results were what I expected.  To do this I simply selected the columns I wanted to inspect and 

Select [date]salescommission From dbo.Tracking 
Here are the results that were returned:

SQL_SUM_Pre
As you can see the results show several sales on one day and several commissions.  In this database we keep track of individual sales but lets say we need to get totals for both the sales and commission for each day in particular.
Select [date], sum(sales)as  sales  sum (commission) as    commission 
From   dbo.Tracking 
Group By 
[date]

 After running this script you can see that the sales and commissions have been summed below,  The dates are out of order because when I imported the spreadsheet I was not paying attention to the data type and I converted it to nvarchar.  I will correct this and show how that is done in another article.


SQL_SUM_Results

Happy Summing

~Yati


0 comments:

Post a Comment