Help with query to calc new field

Question

Help with query to calc new field

Pierca

SSC Enthusiast

Points: 139
More actions
August 26, 2005 at 12:46 am

#89585

Hello, i have this query
SELECT sum(draw) as tot_draw , sum(returns) as tot_returns,
sum(returns + short + notdeliv + damaged) as unsold,
sum(draw-returns-short-notdeliv-damaged) as Netsale
how can i calc a new field "% sale" that is the result
from draw/unsold ?
there is anyone can help me?
thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

One alternative is to use this entire query as a derived table and then select the columns from that, including a new column that can reference draw and unsold.

Another option is to simply add a column where you calculate it using the same columns as unsold does. Like this:

SELECT sum(draw) as tot_draw , sum(returns) as tot_returns,

sum(returns + short + notdeliv + damaged) as unsold,

sum(draw-returns-short-notdeliv-damaged) as Netsale,

draw / sum(returns + short + notdeliv + damaged) AS '% sale'

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Pierca SSC Enthusiast Points: 139 More actions · Answer 2

hello thanks for your fast reply

i've added the row

draw / sum(returns + short + notdeliver+damaged) AS '%sale'

but i get this error

Server: Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

can you help?

thanks

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Well then you need to decide what to do when the divisor is 0. For instance, you could test:

case when draw / sum(returns + short + notdeliver+damaged) AS '%sale' = 0 then null else draw / sum(returns + short + notdeliver+damaged) end AS '%sale'

Or something similar to that. You might also want to do this client-side instead.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Smruti-255597 Old Hand Points: 301 More actions · Answer 4

U can do by this way and get the result

SELECT sum(draw) as tot_draw , sum(retuns) as tot_returns,

sum(retuns + short + notdel + damag) as unsold,

sum(draw-retuns-short-notdel-damag) as Netsale,

sum(draw)/sum(retuns + short + notdel + damag) as '%sale' from test1

Smruti From INDIA

Pierca SSC Enthusiast Points: 139 More actions · Answer 5

thanks for your suggest

now all rows are selectd but i still have the divide by zero error

287 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 2

Divide by zero error encountered.

as i new with sql i need a sample how to build this query with

derived table

can you help?

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

I gave you an example above for the % sale column. If you need more, please post the DDL (your table schema), some sample data and the expected results.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Pierca SSC Enthusiast Points: 139 More actions · Answer 7

Hello Chris

below there is table and data

the query is this i just need to add a "%sale" fields to the results

this field is calculate from tot_draw/unsold

SELECT sum(draw) as tot_draw , sum(returns) as tot_returns,

sum(returns + short + notdeliv + damaged) as unsold,

sum(draw-returns-short-notdeliv-damaged) as Netsale

thanks for your help

CREATE TABLE [dbo].[history] (

[ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[number] [int] NULL ,

[cover-date] [smalldatetime] NULL ,

[trans-date] [smalldatetime] NULL ,

[quantity] [int] NULL ,

[location] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[short] [int] NULL ,

[notdeliv] [int] NULL ,

[deliv-ret] [bit] NULL ,

[newsmag] [bit] NULL ,

[overage] [int] NULL ,

[damaged] [int] NULL ,

[draw] [int] NULL ,

[returns] [int] NULL

) ON [PRIMARY]

"ID","number","cover-date","trans-date","quantity","location","short","notdeliv","deliv-ret","newsmag","overage","damaged","draw","returns"

"C3000009",556,2004-09-02 00:00:00,2004-09-02 00:00:00,1,"CHI",0,1,False,False,0,0,1,0

"C3000009",556,2004-08-05 00:00:00,2004-08-05 00:00:00,1,"CHI",0,1,False,False,0,0,1,0

"C3000009",556,2004-07-01 00:00:00,2004-07-01 00:00:00,1,"CHI",0,1,False,False,0,0,1,0

"C3000009",556,2004-05-30 00:00:00,2004-05-30 00:00:00,1,"CHI",0,1,False,False,0,0,1,0

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

chris - shouldn't it just check for

case when sum(returns + short + notdeliver+damaged) = 0 then null else draw / sum(returns + short + notdeliver+damaged) end AS '%sale'...

as opposed to:

case when "draw /" sum(returns + short + notdeliver+damaged) = 0 then null else draw / sum(returns + short + notdeliver+damaged) end AS '%sale' ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 9

Yes, I noted some copy-paste errors in my suggestion. This query should work:

SELECT sum(draw) as tot_draw , sum(returns) as tot_returns,

sum(returns + short + notdeliv + damaged) as unsold,

sum(draw-returns-short-notdeliv-damaged) as Netsale,

case when sum(returns + short + notdeliv+damaged) = 0 then null else sum(draw) / sum(returns + short + notdeliv+damaged) end AS '%sale'

from dbo.history

But like I said, why not simply do this on the client? You have a column tot_draw, and you have a column unsold, so you can simply calculate the percentage in client code. There you can also apply business rules like what to do when unsold = 0, instead of coding the decision into the query as I have done above.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

It's probably me, but it's one thing I don't get... (I can't figure out what 'draw' means)

In any case, the example will say you have a 1% sale... I don't understand how that can be, since the sum of 'draw' is 4 and the sum of 'unsold' also is 4, therefore the sale should be zero%...?

/Kenneth

Pierca SSC Enthusiast Points: 139 More actions · Answer 11

I've solved with the sql statement thanks for your help

I think that it is better work with TSQL when is possible, that manipulate data on client side; both for performance and to have clean code. Now i have all fields i need on dataset and i can use a simple datagrid and show results.

thanks and regards

pierca