CODEWITHSUNDEEP
sqlsql-servergroup-by
Amarpreet Kaur
Amarpreet Kaur

Reputation: 39

Group function not working on SQL server

I use the below query, but it shows some repeated items. So I use the group function, but it does not work.

SELECT p.productId, p.productName, p.catId, p.subCatId, p.productType,
       p.modelNo, p.picUrl, p.color, p.theme, p.productPrice, p.discountedPrice,
       p.quantity, p.details, p.mainPageDisplay, p.productPageDisplay,
       s.subCatId AS Expr1,
       s.subCatName, s.catId AS Expr2,
       s.rank, s.subCatName AS Expr3
FROM (products p INNER JOIN
      subCategories s ON p.catId = s.catId)
WHERE (p.color = 'red') group By p.productName

This query is working fine, but when I add group by then it's not working.

Upvotes: 2

Views: 37225

Answers (4)

Cyril Gandon
Cyril Gandon

Reputation: 17068

You don't need a GROUP BY for selecting distinct rows, you need DISTINCT:

SELECT DISTINCT p.productId, p.productName, p.catId, p.subCatId, p.productType,
       p.modelNo, p.picUrl, p.color, p.theme, p.productPrice, p.discountedPrice,
       p.quantity, p.details, p.mainPageDisplay, p.productPageDisplay,
       s.subCatId AS Expr1,
       s.subCatName, s.catId AS Expr2,
       s.rank, s.subCatName AS Expr3
FROM (products p INNER JOIN
      subCategories s ON p.catId = s.catId)
WHERE (p.color = 'red')

Upvotes: 5

misguided
misguided

Reputation: 3799

SQL GROUP BY aggregates (consolidates and calculates) column values into a single record value. GROUP BY requires a list of table columns on which to run the calculations.

This link has an example to facilitate your understanding of the concept .

Upvotes: 0

cms_mgr
cms_mgr

Reputation: 2017

Your SELECT statement doesn't contain any aggregate functions, so a GROUP BY statement is not appropriate.

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

You need to use all the columns in the SELECT clause in GROUP BY Clause or otherwise use DISTINCT keyword after SELECT Keyword.

Upvotes: 1

Related Questions