How to Use GroupBy in sql?

Question

0.00/5 (No votes)

See more:

Hi,

I have table like this

SQL

Id 	 ProductName	MainCategory	 AdditionalCategory	size 
1	 American Roast	Coffee		                        10
2	 American Roast	Coffee		                        12
3        American Roast	Coffee		                        14
4	 American Roast	Coffee		                        16
5	 American Roast	Coffee	          Hawaiian	        10
6	 American Roast	Coffee	          Hawaiian	        12
7	 American Roast	Coffee	          Hawaiian	        14
8	 American Roast	Coffee	          Hawaiian	        16
9	 American Roast	Coffee	          Kona	                10
10	 American Roast	Coffee	          Kona	                12
11	 American Roast	Coffee	          Kona	                14
12	 American Roast	Coffee	          Kona	                16

I have use group by class in this table

SQL

select Productname,Maincategory,AdditionalCategory1 from testproducts
group by Productname,Maincategory,AdditionalCategory1

I got Result like this

SQL

ProductName	MainCategory	 AdditionalCategory
American Roast    Coffee  
American Roast    Coffee         Hawaiian
American Roast    Coffee         Kona

But I want Result

XML

Id ProductName MainCategory     AdditionalCategory  Size 
 1  American Roast    Coffee                          10
 5  American Roast    Coffee         Hawaiian         10
 9  American Roast    Coffee         Kona             10

If any possible please give me code

Thanks Advance

Posted 14-May-14 1:26am

Murugesan22

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2014-05-14T01:33:00

Solution 1

If you want the size, then you should include the column in your query :)
And since you seem to only want the minimum size value, then you have to use the MIN() function.

SQL

SELECT Productname, Maincategory, AdditionalCategory, MIN(Size) FROM testproducts
GROUP BY Productname, Maincategory, AdditionalCategory

Hope this helps.

Posted 14-May-14 1:33am

phil.o

Comments

Murugesan22 14-May-14 7:37am

I want Id also If it is possible?

phil.o 14-May-14 7:47am

Then include it in the SELECT clause.

Maciej Los 14-May-14 12:20pm

So, add it and check the results ;)

Maciej Los 14-May-14 12:43pm

Please see my answer for details ;)

Maciej Los · Answer 2 · 2014-05-14T06:42:00

Try this:

SQL

DECLARE @tmp AS TABLE (Id INT IDENTITY(1,1), ProductName VARCHAR(30), MainCategory VARCHAR(30), AdditionalCategory VARCHAR(30), size INT)

INSERT INTO @tmp (ProductName, MainCategory, AdditionalCategory, size)
VALUES('American Roast', 'Coffee', NULL, 10),
('American Roast', 'Coffee', NULL, 12),
('American Roast', 'Coffee', NULL, 14),
('American Roast', 'Coffee', NULL, 16),
('American Roast', 'Coffee', 'Hawaiian', 10),
('American Roast', 'Coffee', 'Hawaiian', 12),
('American Roast', 'Coffee', 'Hawaiian', 14),
('American Roast', 'Coffee', 'Hawaiian', 16),
('American Roast', 'Coffee', 'Kona', 10),
('American Roast', 'Coffee', 'Kona', 12),
('American Roast', 'Coffee', 'Kona', 14),
('American Roast', 'Coffee', 'Kona', 16)


SELECT t2.Id, t1.ProductName, t1.MainCategory, t1.AdditionalCategory, t1.size
FROM (
    SELECT ProductName, MainCategory, AdditionalCategory, MIN(size) AS size
    FROM @tmp
    GROUP BY ProductName, MainCategory, AdditionalCategory
) AS t1 LEFT JOIN @tmp AS t2 ON t1.ProductName = t2.ProductName AND t1.MainCategory = t2.MainCategory
        AND COALESCE(t1.AdditionalCategory, '-') = COALESCE(t2.AdditionalCategory,'-') AND t1.size  = t2.size

Result:

1	American Roast	Coffee	NULL	10
5	American Roast	Coffee	Hawaiian	10
9	American Roast	Coffee	Kona	10

You can't add Id together with MIN() function, because it enforces to add Id to GROUP BY list. The only way to achieve expected output is to use subquery.

pratap420 · Answer 3 · 2014-05-14T04:04:00

Solution 2

Added Id to sol1 .

Select Min(id), ProductName,MainCategory,AdditionalCategory,MIN(size)
from testproducts
Group by ProductName,MainCategory,AdditionalCategory

Posted 14-May-14 4:04am

pratap420