When using GROUP BY, do we always have to group by a selected column?

Question

When using the GROUP BY clause, do we always have to group by one of the selected columns listed after SELECT?

Answer

No, you can GROUP BY a column that was not included in the SELECT statement.

For example, this query does not list the price column in the SELECT, but it does group the data by that column.

SELECT name, downloads
FROM fake_apps
GROUP BY price;

However, usually we do include the grouped by column in the SELECT for the sake of clarity, so that it’s easier to see what rows belong to which group.

17 Likes

I tire it and it did not work out… in the example project on CAmy it gave me only the SUM.

In your example, what name and download will be then displayed in each line?

The display will start with price = 0.0, but what name and downloads will it display then?

In my experience, it will display the name, downloads of the last item having a price = 0.0

This question is confusing.

3 Likes

I tried running i and it gives out a result. Not sure how is it calculating?

can anyone help?

The above code should not be used. Every non aggregate column used in SELECT statement must be added to GROUP BY clause.

Hi All,

I have tried, That wont work for me,

My Query was given below,

-- 'GROUP BY' Clause
-- It is Clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange
-- identical data into groups.
-- This 'GROUP BY' Statement comes after any 'WHERE' Statement, but before 'ORDER BY' OR 'LIMIT'

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='GroupClause')
BEGIN
	TRUNCATE TABLE GroupClause
	DROP TABLE GroupClause
END
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='GroupClause')
BEGIN
	CREATE TABLE GroupClause
	( 
		ID INT,
		MovieName VARCHAR(200),
		ReleaseYear VARCHAR(200),
		UserRating FLOAT,
);
	INSERT INTO GroupClause(ID, MovieName,ReleaseYear,UserRating)
				   VALUES (1,'Iron Man','2009',86.2),
					(2,'Win Man','2018',86.1),
					(3,'Heartin Man','2008',25.2),
					(4,'Lucky Man','2009',34.45),
					(5,'Warner Man','2008',25.23),
					(6,'Evil Man','2010',67.1),
					(7,'Spider Man','2010',78.98),
					(8,'Lone Ranger','2015',67.11),
					(9,'Green Lanrern','2011',87.22),
					(10,'Wonder Woman','2012',34.45),
					(11,'X Men','2012',99.12),
					(12,'Fast and Furious','2012',100),
					(13,'Failure Man','2013',86.1),
					(14,'Successful Man','2013',55.35),
					(15,'Pain Man','2014',12.12),
					(16,'Cry Man','2018',24.24),
					(17,'Vanish Man','2015',36.35),
					(18,'Torque Man','2010',06.87),
					(19,'Stupid Man','2018',55.98),
					(20,'Gaint Man','2008',56.76),
					(21,'Faithful Man','2014',57.90);
END

IF EXISTS(SELECT * FROM GroupClause)
BEGIN
	SELECT ROUND(AVG(UserRating),2) AS AvgRating, ReleaseYear FROM GroupClause
	GROUP BY ReleaseYear

	SELECT SUM(ID), MovieName,ReleaseYear FROM GroupClause
	GROUP BY UserRating
	
END

My SQL Version :

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5

Thanks,
Thiruanvukkarasu G