Need to summarize line items. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to summarize line items.

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-06 : 13:28:11

I have a different problem here. (I don't have to worry that much about the category because the report is not showing by category.

My problem here is that this SQL produces the items sold and the price. The problem is that often the same item sold at a different price. The need is to show the items and qty summarized and the price sold to be an average. Ideally, It could all be done in this SQL.

SELECT SUM(SOP10200.XTNDPRCE) AS Price, SUM(SOP10200.QUANTITY)
AS quantity, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0)
AS ID,
IV00101.ITEMDESC, IV00101.ITMGEDSC, SOP10200.UNITPRCE, sop10106.USRDEF05

FROM dbo.SOP10200 AS SOP10200
INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE AND SOP10100.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10100.SOPNUMBE = dbo.SOP10106.SOPNUMBE

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 14:15:33

what's your question?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-06 : 14:22:17

I am not certain how to make a summarization of items, the Itemnmbr, and at the same time I need an average price per item.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 15:07:23

If possible do all the aggregations on reporting rather than in the data query.
Although here you need a GROUP BY clause to get AVG or SUM of the amount fields.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-06 : 15:22:26

I agree, but this is how they already did it.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 15:48:02

then use GROUP BY to summarize results.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-06 : 15:57:07

RK, Yes that I can do but am not sure how to use the Averaging.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 16:02:12

SELECT COL1, SUM(COL2) AS SUM_COL2, AVG(COL3) AS AVG_COL3, COUNT(1) AS COL1_COUNT
FROM MY_TABLE
GROUP BY COL1

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-06 : 17:14:12

Rohit, this SQL already has a groupby in it. Are you suggesting that I am to add a second group by, or would I organize this new grouping within the existing one?

SET @SQL = @SQL + ' GROUP BY IV00101.ITMGEDSC, IV40600.USERCATLONGDESCR, IV00101.ITEMDESC, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0), SOP10200.UNITPRCE, sop10106.USRDEF05'

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 17:26:07

you just need one group by, but its difficult to guess looking at partial query. can you post all of it?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-07 : 09:01:30

Well it's one of those that is used in the asp.net interface :

ALTER Proc [dbo].[USP_QuotesSummary_DrillDown]
@FromDate VARCHAR(25), @ToDate VARCHAR(25), @CustName varchar(250), @Department varchar(4000), @Category varchar(4000),
@SalesTypes Varchar(50)
as

DECLARE @SQL VARCHAR(8000)

if @CustName <> 'ALL'
begin
set @CustName = replace(@CustName,',', ''',''')
set @CustName = '''' + @CustName + ''''
end

if @Department <> 'ALL'
begin
set @Department = replace(@Department,',', ''',''')
set @Department = '''' + @Department + ''''
end

if @Category <> 'ALL'
begin
set @Category = replace(@Category,',', ''',''')
set @Category = '''' + @Category + ''''
end

set @SalesTypes = replace(@SalesTypes,',', ''',''')
set @SalesTypes = '''' + @SalesTypes + ''''

SET @SQL = 'SELECT SUM(SOP10200.XTNDPRCE) AS Price, SUM(SOP10200.QUANTITY) AS quantity, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0) AS ID,
IV00101.ITEMDESC, IV00101.ITMGEDSC, IV40600.UserCatLongDescr as CatD,
SOP10200.UNITPRCE, sop10106.USRDEF05

FROM dbo.SOP10200 AS SOP10200 INNER JOIN
dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE
AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE AND
SOP10100.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10100.SOPNUMBE = dbo.SOP10106.SOPNUMBE

inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC

Where SOP10100.DOCDATE between ''' + @FromDate + ''' and ''' + @ToDate + ''''

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND IV00101.ITMGEDSC in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND sop10106.USRDEF05 in (' + @Department + ')'
END

IF @CustName <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.rm00101.CUSTNAME in (' + @CustName + ')'
END

IF @SalesTypes <> ''
BEGIN
SET @SQL = @SQL + ' AND SOP10200.SOPTYPE in (' + @SalesTypes + ')'
END

SET @SQL = @SQL + ' GROUP BY IV00101.ITMGEDSC, IV40600.USERCATLONGDESCR, IV00101.ITEMDESC, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0), SOP10200.UNITPRCE, sop10106.USRDEF05'
--print @SQL
EXEC (@SQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources