Partial Grouping-Grouping on few columns of select list

Question

question

Binod asked Dec 23, '14

Partial Grouping-Grouping on few columns of select list

Hello All, Greetings, will it be possible to achieve this. PT , MK , MO , DATE, COMMENT , NO Camber, AUTO, F-250, 1993, REDWHEEL, MOOK8746 Camber, AUTO, F-250, 1993, REDWHEEL, MOOK8746 Camber, AUTO, F-250, 1993, REDWHEEL, MOOK8746 Camber, AUTO, F-250, 1993, REDWHEEL, ZOOK8746 Camber, AUTO, F-250, 1993, REDWHEEL, ZOOK8747 Camber, AUTO, F-250, 1993, REDWHEEL, ZOOK8748 Camber, AUTO, F-250, 1993, REDWHEEL, ZOOK8749 Caster, Ford, MXT, 1989, MAHADEV , MOOK8746 Caster, Ford, MXT, 1989, VISHNU , MOOK8746 Caster, Ford, MXT, 1989, MAHADEV , ZOOK8747 Caster, Ford, MXT, 1989, VISHNU , ZOOK8748 The above is the table structure. i need to create a group on PT,MK,MO,DATE and need to check there is only 1 distinct comment available for distinct NO, and if so, need to exclude from output. **OUTPUT IS BELOW** PT , MK, MO, DATE, COMMENT, NO Caster, Ford, MXT, 1989, MAHADEV, MOOK8746 Caster, Ford, MXT, 1989, VISHNU, MOOK8746 Caster, Ford, MXT, 1989, MAHADEV, ZOOK8747 Caster, Ford, MXT, 1989, VISHNU, ZOOK8748 we can see output contain more than 1 distinct comment per NO. Thanks a lot. and Wish u all a very happy Christmas.

sql-server-2005 t-sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-12-23T14:30:26Z

Tom Staab answered Dec 23, '14

It sounds like you actually want distinct values across all columns. If you are grouping only by those first 4 to capture a total, you could use grouping sets to do both. Edit: If you group by those 4 columns and then add HAVING COUNT(DISTINCT comment) > 1 at the end (but before your ORDER BY), it will exclude any group that does not have more than 1 comment. You might be able to just use COUNT(*), but I used COUNT(DISTINCT comment) because you specifically mentioned that. If the NO and Comment columns go hand-in-hand, the COUNT(*) should work and perform faster.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Binod commented · Dec 23, 2014 at 02:41 PM

Hi Tom,Here Data is from 2 different group. 1st group (Camber) and 2nd group (Caster).i need to grouping only by first 4 to check how many distinct value in a comment, and if it is only 1 comment per group i want to exclude it from result set.here caster group is having 2 different comment (Mahadev and Vishnu), so it retain in result set.

0 ·

Binod commented · Dec 24, 2014 at 03:17 PM

Thanks Tom, please help me how to have all columns in select and only few column in group by with having clause.need to group by those 4 columns and then HAVING COUNT(DISTINCT comment) > 1. but result set contain all 6 column. Please suggest.

0 ·

Tom Staab ♦ commented · Dec 24, 2014 at 03:25 PM

It sounds like you want to output columns that aren't in the grouping and don't use an aggregate function. I believe you will need to use a CTE with the grouping and having. Then select your data from the original dataset where those first 4 columns match the results of the CTE.

0 ·

question