Help with query to calc new field
-
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
-
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'
-
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
-
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.
-
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
-
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?
-
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.
-
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
-
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 !!!** -
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.
-
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
-
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply