sql group by order by problem in C#

Question

i have table STOCK look like dis S_en S_P_name S_supName S_qty_avail S_Pur_rate S_batchno S_Expdate 1 crocin aaa 5 10 b8 jul-14 2 disprin bbb 8 8 d8 may-20 3 crocin Opening 2 0 aj9 apr=20 4 combiflame kkk 4 2 s9 may-15 5 disprin kkk 3 5 k9 aug-21 here u can see disprin has two Supplier one is bbb and other is kkk ... I want my resultant table should be shown like this in datagridview S_no S_P_name S_supName S_qty_avail S_Pur_rate 1 crocin aaa (5+2=)7 10 2 disprin kkk (8+3=) 11 5 3 combiflame kkk 4 2 hi friends  here u see above  medicine name should not be repeated and see case of Crocin == this product has Sup_name 'aaa' and 'Opening' too the line selected in pink color in table this should not be shown in result table means P_name having Opening as their Sup_name should not be shown .. clear . - the column marked with blue color is only to explain u the order of showing Sup_name (i.e. acc to their min Pur_rate) we dont want it to be shown in result . now u can see the resultant table is sorted alphabetically in S_sup_name ... and those names are shown which are providing product with min pur_rate thanks for reading. help me in this plz

Khan Abrar Ahmed · Answer

Hi try this and u can change the order by clause as per your need DECLARE @tempTable AS TABLE ( S_en INT  S_P_name VARCHAR(64)  S_supName VARCHAR(64)  S_qty_avail INT  S_Pur_rate INT  S_batchno VARCHAR(64)  S_Expdate DATE  S_totalGross INT ) INSERT INTO @tempTable ( S_en S_P_name S_supName S_qty_avail S_Pur_rate S_batchno S_Expdate S_totalGross ) VALUES ( 1 'crocin' 'aaa' 5 10 'b8' '01-Jul-2014' 22 ) ( 2 'disprin' 'bbb' 8 8 'd8' '01-Jul-2020' 30 ) ( 3 'crocin' 'Opening' 2 0 'aj9' '01-Apr-2020' 20 ) ( 4 'combiflame' 'kkk' 4 2 's9' '01-Jul-2015' 15 ) ( 5 'disprin' 'kkk' 3 5 'k9' '01-Jul-2021' 12 ); WITH temp AS( SELECT S_P_name  Max(S_supName) AS S_supName  ( SELECT SUM(S_qty_avail) FROM @tempTable s WHERE s.S_P_name = r.S_P_name GROUP BY S_P_name ) AS S_qty_avail  MIN(S_Pur_rate) AS S_Pur_rate FROM @tempTable r WHERE S_supName 'Opening' GROUP BY S_P_name ) SELECT ROW_NUMBER() OVER(ORDER BY a.S_supName desc) S_P_name  S_supName  S_qty_avail  (SELECT MIN(S_Pur_rate) OVER (PARTITION BY S_P_name) FROM @tempTable s WHERE s.S_supName=a.s_supname AND a.S_P_name = s.S_P_name)as S_Pur_rate FROM temp a ORDER BY a.S_supName desc

varsha dodiya · Answer

well let me see if it works in implementing visuAL STUDIO 2010

varsha dodiya · Answer

@Abrar Finally ! thank u so much .. u must b very intelligent in sql impressive .thanks again

varsha dodiya · Answer

@Abrar combiflame kkk 4 2 disprin bbb 11 5 crocin aaa 5 10 here Sup_name is not alphabetically sorted and disprin is showing SUP_name 'bbb' which is not correct Supllier whose pur_rate is min ? but in pur_rate column rate is fetched min omly this particular column is correct but associated sup_name is wrong

varsha dodiya · Answer

@Jignesh i have sorted with errors but it is also giving same result as @Abrar's last given query ... which is showing incorrect data (diff Sup_name with diff min Pur_rate) :((((

Khan Abrar Ahmed · Answer

if still u have the issue can you please send the output.

varsha dodiya · Answer

error in S_qty_svail S_supName S_Pur_rate

Jignesh Trivedi · Answer

hi can you please share error description?

varsha dodiya · Answer

@jignesh ur query is giving error i have check if column names are written correct or not .. then too error.

varsha dodiya · Answer

@Abrar lolz ... now it seems u sorted columns separately .. table is concise in result but showing min cost with name of another sup_name which is making it in correct . M not that deep into sql so help me plz. i'm having database in visual studio 2010

Khan Abrar Ahmed · Answer

Hi Versha please check below script. DECLARE @tempTable AS TABLE ( S_en INT  S_P_name VARCHAR(64)  S_supName VARCHAR(64)  S_qty_avail INT  S_Pur_rate INT  S_batchno VARCHAR(64)  S_Expdate DATE  S_totalGross INT ) INSERT INTO @tempTable ( S_en S_P_name S_supName S_qty_avail S_Pur_rate S_batchno S_Expdate S_totalGross ) VALUES ( 1 'crocin' 'aaa' 5 10 'b8' '01-Jul-2014' 22 ) ( 2 'disprin' 'bbb' 8 8 'd8' '01-Jul-2020' 30 ) ( 3 'crocin' 'Opening' 2 0 'aj9' '01-Apr-2020' 20 ) ( 4 'combiflame' 'kkk' 4 2 's9' '01-Jul-2015' 15 ) ( 5 'disprin' 'kkk' 3 5 'k9' '01-Jul-2021' 12 ); WITH temp AS( SELECT S_P_name  MIN(S_supName) AS S_supName  ( SELECT SUM(S_qty_avail) FROM @tempTable s WHERE s.S_P_name = r.S_P_name GROUP BY S_P_name ) AS S_qty_avail  MIN(S_Pur_rate) AS S_Pur_rate FROM @tempTable r WHERE S_supName 'Opening' GROUP BY S_P_name ) SELECT ROW_NUMBER() OVER(ORDER BY a.S_supName desc) S_P_name  S_supName  S_qty_avail  (SELECT MIN(S_Pur_rate) FROM @tempTable s WHERE s.S_supName=a.s_supname AND a.S_P_name = s.S_P_name)as S_Pur_rate FROM temp a ORDER BY a.S_supName desc

Jignesh Trivedi · Answer

Hey Varsha Are you tried my solution... It give you correct result but there is only one problem two queries are fired :) hope this will help you.

varsha dodiya · Answer

@Abrar this is near to the desired result but not perfect. this is not showing P_name having 'Opening' which is correct but other P_name which are shown should b with the Sup_name having min Pur_Rate (this is not achieved yet) .. table what i get is combiflame kkk 4 2 disprin bbb 11 8 crocin aaa 7 10 also it is not alphabatically SUP_name

Jignesh Trivedi · Answer

Hi try following query select A.S_P_nameA.S_supName(A.S_qty_avail + ISNULL(B.S_qty_avail0)) as S_qty_availA.S_Pur_rate from ( SELECT S_P_name  MIN(S_supName) AS S_supName  SUM(S_qty_avail) AS S_qty_avail  Min(S_Pur_rate) AS S_Pur_rate FROM STOCK WHERE S_supName 'Opening' GROUP BY S_P_name) A Left outer join (SELECT S_P_name  MIN(S_supName) AS S_supName  SUM(S_qty_avail) AS S_qty_avail  Min(S_Pur_rate) AS S_Pur_rate FROM STOCK WHERE S_supName ='Opening' GROUP BY S_P_name ) B on A.S_P_name = b.S_P_name hope this will help you.

Khan Abrar Ahmed · Answer

Hi Varsha in the above script there is line SELECT * FROM @tempTable AS tt Please comment this line and dont execute this line.

Khan Abrar Ahmed · Answer

Hi Varsha try this if still not working so can you please send the your output SELECT ROW_NUMBER() OVER ( ORDER BY MIN(S_Pur_rate) DESC ) AS S_no  S_P_name  MIN(S_supName) AS S_supName  ( SELECT SUM(S_qty_avail) FROM @tempTable s WHERE s.S_P_name = r.S_P_name GROUP BY S_P_name ) AS S_qty_avail  MIN(S_Pur_rate) AS S_Pur_rate FROM @tempTable r WHERE S_supName 'Opening' GROUP BY S_P_name ORDER BY MIN(S_Pur_rate) DESC  S_P_name

varsha dodiya · Answer

@Abrar i'm glad that u understand what i want ... but i tried by applying ur query its giving repeated result for both P_name and P_sup_name column i dont want P_name to be repeated ..

Khan Abrar Ahmed · Answer

Hi try this DECLARE @tempTable AS TABLE ( S_en INT  S_P_name VARCHAR(64)  S_supName VARCHAR(64)  S_qty_avail INT  S_Pur_rate INT  S_batchno VARCHAR(64)  S_Expdate DATE  S_totalGross INT ) INSERT INTO @tempTable ( S_en S_P_name S_supName S_qty_avail S_Pur_rate S_batchno S_Expdate S_totalGross ) VALUES ( 1 'crocin' 'aaa' 5 10 'b8' '01-Jul-2014' 22 ) ( 2 'disprin' 'bbb' 8 8 'd8' '01-Jul-2020' 30 ) ( 3 'crocin' 'Opening' 2 0 'aj9' '01-Apr-2020' 20 ) ( 4 'combiflame' 'kkk' 4 2 's9' '01-Jul-2015' 15 ) ( 5 'disprin' 'kkk' 3 5 'k9' '01-Jul-2021' 12 ) SELECT * FROM @tempTable AS tt SELECT ROW_NUMBER() OVER ( ORDER BY Min(S_Pur_rate) desc ) AS S_no S_P_name  MIN(S_supName) AS S_supName  SUM(S_qty_avail) AS S_qty_avail  Min(S_Pur_rate) AS S_Pur_rate FROM @tempTable WHERE S_supName 'Opening' GROUP BY S_P_name ORDER BY Min(S_Pur_rate) desc S_P_name

Lakshmanan Sethu · Answer

Hi you need to use group by clause for S P name column. then you can get the desired result.