Summarizing Data

[cfriedberg]

(If you copy the code into an editor without proportional fonts, the columns will line up.)

I have the following table:
BCCODE BDATE BUV BPRICE BQUAN Ext. Price
5069 7/1/2002 0 0.11 62 6.82
5069 7/1/2002 0 1.1 38 41.8
5069 7/6/2002 6.977 150 1 150
5069 7/3/2002 18.605 400 2 800
5069 7/3/2002 18.605 400 1 400
5069 7/1/2002 18.605 400 2 800
5069 7/2/2002 1.86 40 8 320


I want to summarize the data to show three sums:
Total sum should be 2518.62
Partial sum based on BUV (unit value)
If BUV is 0 the Sum should be 48.62
If BUV is not 0 the Sum should be 2470.00

I have been trying to use Select statements such as this
Select BCCODE, 0,0,
Case When (BUV)=0 Then Sum(BQUAN * BPRICE) End
From bil
WHERE (BCCODE = '5069') AND (BDATE BETWEEN CONVERT(DATETIME, '2002-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2002-07-07 00:00:00', 102))
GROUP BY BCCODE, BUV with Rollup
having (BUV)=0. This one gives me the correct answer when the BUV is 0.

BUUUUUUTTTTT.....

Select BCCODE, 0,
Case When BUV<>0 then SUM(BQUAN * BPRICE) End, 0
FROM bil
WHERE (BCCODE = '5069') AND (BDATE BETWEEN CONVERT(DATETIME, '2002-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2002-07-07 00:00:00', 102))
GROUP BY BCCODE,BUV with Rollup
Having BUV<>0

Gives me three records, not one.

It would be nice to have these items in one record.

Any help would be appreciated.

Carolyn

 

[Antzz]

In this case you will have to ommit the Group By for BCCCode. Once you do that and only have BUV in your GROUP BY clause, your problem should be solved.

 

[Antzz]

Also you will have to ommit BCCCode from the SELECT statement. Sorry I did not add it in my previous post.

 

[tlbroadbent]

Omit BUV from the Group By statement not BCCCode. Omit the HAVING clause.

Put the entire CASE function inside the SUM function. Add an alias for the computed value.

Times are not needed on the date comparison. '00:00:00' is assumed if omitted.

Select
BCCODE,
0,
TotValue=Sum(Case When BUV<>0 then BQUAN * BPRICE End),
0
FROM bil
WHERE BCCODE = '5069'
AND BDATE BETWEEN
CONVERT(DATETIME, '2002-07-01', 102) AND
CONVERT(DATETIME, '2002-07-07', 102)
GROUP BY BCCODE with Rollup Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[cfriedberg]

Thank you tbroadbent. Your suggestion works perfectly. I have replace about 120 lines of code with this one-line command. The full code is now

SELECT CECV_LedgerAccount AS 'Ledger A/C', 'SALES' AS [Ledger A/C Name], dbo.bil.BCCODE, CEC_ClientName,
BINV,
Sum (BQUAN * BPRICE) AS [Total Billing],
Sum(Case When BUV<>0 then BQUAN * BPRICE End) AS [Labor Billing],
Sum(Case When BUV=0 then BQUAN * BPRICE End) AS [Material Billing],
CONVERT(DATETIME, '2002-07-01', 102),
CONVERT(DATETIME, '2002-07-07', 102)
FROM bil
INNER JOIN
CEClientVariables ON bil.BCCODE = CEClientVariables.CECV_ClientNumber
INNER JOIN CEClient ON bil.BCCODE = CEClient.CEC_ClientNumber
WHERE (BDATE BETWEEN CONVERT(DATETIME, '2002-07-01', 102) AND CONVERT(DATETIME, '2002-07-07', 102))
AND (BINV IS NOT NULL)
GROUP BY BINV, BCCODE,CEClientVariables.CECV_LedgerAccount, CEClient.CEC_ClientName

The execution is slick and without the use of cursors.