Group function not working on SQL server

Question

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.

group by is work with only aggregate function or column by which its grouuped by.. — Amit Singh, Apr 18 '13 at 09:58
When you use `GROUP BY`, all columns need to be either in the `GROUP BY` or contained in an aggregate - so you need to tell us/SQL Server what you want to do with each column that isn't `productName` - should we take the `MAX()` or `MIN()` value for each of those columns? — Damien_The_Unbeliever, Apr 18 '13 at 10:06
it's showing 3 time Product name like pendrive then next value 100 in 3 time,after that it will pick nxt item ,laptop — Amarpreet Kaur, Apr 18 '13 at 10:21
So, as I said in my first comment - you need to tell us how to select an appropriate value for each column that *isn't* `productName`. You either need to define an aggregate, or, if you want to say "I want the first/last row's values", you need to tell us what ordering to use to define the first or last row. — Damien_The_Unbeliever, Apr 19 '13 at 06:42
Have you tried something like this? http://stackoverflow.com/questions/38495465/how-to-group-by-multiple-columns-in-sql-server — AndreFeijo, Jul 21 '16 at 05:48
As @Damien_The_Unbeliever has explained above. The engine can only display a single row for each group. You're selecting other rows (that aren't part of GROUP BY clause). For each [ProductName] there can be different values for other columns. Hence the query fails. Because those rows can't be determined to be displayed with your group name column. — Arpit Chinmay, Aug 11 '21 at 20:30

Cyril Gandon · Answer 1 · 2013-04-18T10:12:12.423

5

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')

edited Apr 18 '13 at 10:12

answered Apr 18 '13 at 09:59

Cyril Gandon

16,830
14
78
122

This means that you want distinct product, and something about subcategories which is group. Show us your data, and what your want. – Cyril Gandon Apr 18 '13 at 10:13
these are column value repeated in 3 times 24 KINGSTON (four pc. set) 2 13 – Amarpreet Kaur Apr 18 '13 at 10:24

cms_mgr · Answer 2 · 2013-04-18T10:05:20.903

1

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

edited Apr 18 '13 at 10:05

answered Apr 18 '13 at 09:58

cms_mgr

1,977
2
17
31

yes that's error ,bt i use DISTINCT also ,but showing same reult – Amarpreet Kaur Apr 18 '13 at 10:10

score 0 · Answer 3 · answered Apr 18 '13 at 10:05

0

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

answered Apr 18 '13 at 10:05

Santhosh

1,771
1
15
25

score 0 · Answer 4 · edited May 23 '17 at 11:55

0

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 .

edited May 23 '17 at 11:55

Community

1
1

answered Apr 18 '13 at 10:05

misguided

3,699
21
54
96

Group function not working on SQL server

4 Answers4