asked
Two Way Brainstroming Grouping in sql
Respected All, With Lots of Hope i am seeking help from you all.This is only Sample Record My Table Looks Like:- Table 1 COLA, COLB, COLC, SKU, DESIGN, Suzuki, Commodore Series, 2003, PIA97055,FWD Suzuki, Commodore Series, 2003, PIA97055,AWD Suzuki, Commodore Series, 2003, PIA97045,FWD Suzuki, Commodore Series, 2003, PIA97045,AWD Suzuki, Commodore Series, 2003, PIA95055,FWD Suzuki, Commodore Series, 2003, PIA95055,AWD Suzuki, Century Series 60,2003, WET72-86490,FWD Suzuki, Century Series 60,2003, WET72-86490,AWD Suzuki, Century Series 60,2003, WET72-86488,FWD Suzuki, Century Series 60,2003, WET72-86488,AWD Suzuki, Century Series 60,2003, EWFHAP-1003,AWD Suzuki, Century Series 60,2003, EWFHAP-1111,FWD How to get how many Design Available for Suzuki , Commodore,2003 group or Suzuki, Century Series 60, 2003 group Then Value Present in SKU column for the same group must be available with all value present in Design column of same group So Output should not contain last two rows since it does not have record with both AWD and FWD Value in design Please Please Help Me. Thank You All.
or maybe try this select t.* from table t inner join ( select sku from table group by sku having count(distinct design) = (select count(distinct design) from table) ) s on t.sku = s.sku
Here is a page with some examples. The best way to learn is by doing.
http://www.w3schools.com/sql/sql_groupby.asp Cheers!
Thanks AJ, i am able to group it but real challenge for me is how to get records with Value Present in SKU column for the group which contains all value available in Design Column for the same group. for eg: if Group (suzuki,800,2012) contains SKU (A100,B100,C100) and same group i.e (suzuki,800,2012) contain 2 value in Design (AWD,FWD), THEN i need to write query which retrive data only when SKU Contain (A100,AWD)(A100,FWD). here A100 should be with both AWD and FWD. Thanks a lot
SELECT COLA, COLB, COLC, SKU FROM yourtable GROUP BY COLA, COLB, COLC, SKU HAVING COUNT(*) = 2
Thank You Very Much Squirrel...... but it will give output for SKU which have two value in design column as 2, but value present in design is not constant as (AWD,FWD), design column may contain (AWD,FWD,SWD)OR only (AWD), value available in Design depends upon after grouping Col A, ColB and ColC.i Need to design query which result only all those SKU which contain All available option of Design value in a particular group. kindly share your expertise knowledge. Have a great day.
I need to get only those SKU (for eg:- PIA97055) records which is available with all Design type available from same group of ColA, ColB and ColC. Here design type is not constant so we can't do having count (*)=2, it may be only 1 design type (for eg:- AWD) value for one group of ColA, ColB and ColC. Or it may have 3 design type (for Eg:- AWD,TWD,SDF) value for one group of ColA, ColB and ColC. I need to get only all those records when SKU available With all design we got with that group. means SKU from same group available with all design available in that same group. if SKU from same Group is not present with all design of same group then it should not appear in result. Kindly share your views. from above example, my query gives records only PIA97055 with AWD and PIA97055 with AWD,PIA97055 with TWD,PIA97055 with SDF
16 People are following this question.
