group by clause in sql query

Question

friends  I am trying to extract data performing some arithmetic operation (division and modules) for desired result i applied query SELECT S_P_name S_P_pack SUM(S_P_ttoqty) / S_P_pack AS ost  SUM(S_P_ttoqty) % S_P_pack AS otb SUM(S_P_ssold) / S_P_pack AS sst  SUM(S_P_ttsold) % S_P_pack AS stb SUM(S_P_sqty) / S_P_pack AS pst  SUM(S_P_ttqty) % S_P_pack AS ptb SUM(S_P_savail) / S_P_pack AS ast  SUM(S_P_ttavail) / S_P_pack AS atb SUM(S_P_grsv) AS grs FROM STOCK GROUP BY S_P_name S_P_pack it is giving me result as i want but ost  sst pst and ast are giving result of devision in decimal (like 11.916666) but i want it to show like 11 only help me in getting this value type. thanks in advance . :)

Khan Abrar Ahmed · Answer

Hi try the Floor syntex try to use below script SELECT S_P_name S_P_pack FLOOR(SUM(S_P_ttoqty) / S_P_pack) AS ost SUM(S_P_ttoqty) % S_P_pack AS otb FLOOR(SUM(S_P_ssold) / S_P_pack) AS sst SUM(S_P_ttsold) % S_P_pack AS stb FLOOR(SUM(S_P_sqty) / S_P_pack) AS pst SUM(S_P_ttqty) % S_P_pack AS ptb FLOOR(SUM(S_P_savail) / S_P_pack) AS ast SUM(S_P_ttavail) / S_P_pack AS atb SUM(S_P_grsv) AS grs FROM STOCK GROUP BY S_P_name S_P_pack Please mark as answer if it help you

varsha dodiya · Answer

oops  silly me forgot abt FLOOR . :) thank u