Skip to Content

Top 20 Sales Item (Item Group Wise)

Hi Experts,

Following Query is retrieving top 20 sales items between a date range.

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry

LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

Now I wand to generate this report based on item group separately.

Please include item group selection parameter in this query.

Thanks & Regards,

ABDUL RASHEED P

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 14, 2015 at 02:36 PM

    Hi,

    Try:

    SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency,T4.[ItmsGrpNam]

    FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry

    LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OITB T4 ON T4.ItmsGrpCod = T2.ItmsGrpCod

    WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and T4.[ItmsGrpNam] = '[%2]'

    GROUP BY T1.ItemCode, T2.ItemName, T1.Currency,T4.[ItmsGrpNam]

    order by sum(T1.Quantity) desc

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 10:24 AM

    Hi,

         Use the below query

    SELECT TOP 20 T4.ItmsGrpNam,T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

    FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry

    LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

    inner join OITB T4 ON T4.ItmsGrpCod=T2.ItmsGrpCod

    WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1]

    GROUP BY T4.ItmsGrpNam,T1.ItemCode, T2.ItemName, T1.Currency

    order by sum(T1.Quantity) desc

    Regards,

    Manish

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 10:33 AM

    hi

    Try

    This One

    SELECT TOP 20 T1.ItemCode, T2.ItemName, T3.[ItmsGrpNam], cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

    FROM  INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

    WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and  T3.[ItmsGrpNam] =[%0]

    GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

    order by sum(T1.Quantity) desc

    Thanks&Regards

    Andakondaramudu

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Sir,

      When executing this query am getting an error message.

      [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command '(SWEI)(SWEI)