Note: You can go write to Microsoft’s documentation on SELECT – GROUP BY by clicking this link: http://bit.ly/2NMiV35.
I wanted to learn more about the group and count functionalities in T-SQL. When I have tried to use them in the past I get frustrated by the error that says:
Msg 8120, Level 16, State 1, Line 4 Column 'AdventureWorks2014.Production.Product.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
But obviously I need data to work with. Instead of using data from the database at the office I decided to use AdventureWorks2014. I get frustrated when I can’t try something and I can’t try it because I can’t afford to mess anything up. And I don’t want to create database copies on the existing server for performance reasons. And I don’t want to create copies on my home server for privacy reasons. So, I have decided to get very well acquainted with the Adventure Works database.
The Adventure Works 2014 database resides on my SQL Express Server instance on my Windows Server 2008 R2 running at the house. This server primarily serves as the file server and various other minor roles. I can connect locally via integrated network authentication but normally I connect remotely use server authentication. Bottom line on why is that I prefer one development environment and having SSMS, etc. at the same place is more efficient in my experience (not SQL Server experience, but other experience).
First off, I need to answer the question, “What is the Adventure Works database?” This database comes from Microsoft and is supposed to be from the fictional company Adventure Works Bicycles. The data is available from different years, in different forms, and even for different Microsoft products beyond just SQL Server. See Microsoft’s download page here: http://bit.ly/2OdW4wF. There are OLTP downloads, Data Warehouse downloads, creation scripts, etc. I want to expand on this more but now is not the time. However, the Adventure Works database is described in this concise post by Dhananjay Kumar Upadhyay: http://bit.ly/2xYZr0i.
I went poking around tables that looked like they would have data I could group by month or year or something. I finally happened upon the table [AdventureWorks2014].[Production].[Product]. I will describe this table more later.
I was able to create the error above by running this query:
USE AdventureWorks2014 GO SELECT * FROM [AdventureWorks2014].[Production].[Product] GROUP BY SafetyStockLevel GO
Again, the output was:
Msg 8120, Level 16, State 1, Line 4 Column 'AdventureWorks2014.Production.Product.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
But with the following query, adapted from a good post on stackexchange.com (http://bit.ly/2R0P9FI), I got what I sought:
USE AdventureWorks2014 GO SELECT SellStartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, SellStartDate), 0), ReorderPoint, COUNT(Name) TotalCount FROM [AdventureWorks2014].[Production].[Product] WHERE SellStartDate >= '2000-02-01' AND SellStartDate <= '2012-12-31' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SellStartDate), 0), ReorderPoint; GO
Which yielded:
Now I have two different queries: one that works and one that does not. The question I want to answer next is, “What is the difference and what is the GROUP BY syntax and structure?”
If you have any suggestions, questions, or want to undertake learning T-SQL as well let me know in the comments. Eventually I will add a contact form to the blog.