group by clause in sql query

varsha dodiya

12y1.4k

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 . :)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed12y ago
Accepted 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 dodiya12y ago
oops , silly me forgot abt FLOOR . :) thank u