Tech: SQL GROUP BY with SUMFrom 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], sales, commission From dbo.TrackingHere are the results that were returned:
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
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.