Help with SQL if statement and adding fields together

I have a query that I need a hand on. I am trying to add togther some
fiends based on values of another.

What I would like to add a billing total by saying more or less the
following:

SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559

I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.On 18 Mar 2005 07:56:07 -0800, Rob Kopp wrote:
(snip)
>(
>expidite_fee
>IF include_repair_cost = 1
>+ (labor_hours * labor_cost)
>IF include_flat_rate = 1
>+ flat_rate
>IF include_cal = 1
>+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
>BY dateCAL DESC),0)
>IF include_parts = 1
>+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
>repair_partsID WHERE orderID=79559),0) +
>ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
>misc_part_assocID WHERE orderID=79559),0)
>) AS billing_total
>FROM view_inventory
>WHERE orderID=79559
>I know the IF part is whacked, that's where I need the help. Is this
>type of thing even possible? Or even efficent?

Hi Rob,

You'll need to use CASE:

(
expidite_fee +
CASE WHEN include_repair_cost = 1
THEN (labor_hours * labor_cost)
ELSE 0 END +
CASE WHEN include_flat_rate = 1
THEN flat_rate
ELSE 0 END +
CASE WHEN include_cal = 1
THEN ISNULL((subquery cal_cost), 0)
ELSE 0 END +
CASE WHEN include_parts = 1
THEN ISNULL((subquery gptotal), 0) +
ISNULL((subquery gnptotal), 0)
ELSE 0 END
) AS billing_total

> Is it wise to subquery
>for totals (not like I have a choice based on the application
>requirements)? help.

Well, you can do some things to speed up the query.

Since you use the same subquery in two places, you could use a derived
table. Like this:

SELECT a, b, c, a + b + c AS GrandTotal
FROM (SELECT complicated_expression AS a,
complicated_expression AS b,
complicated expression AS c
FROM YourTable
WHERE ...) AS x

Another possibility is to use a join between your inventory table and
derived tables where the grouping has already been done:

SELECT ...,
gptotal,
...,
complicated expression using gptotal,
...
FROM view_inventory AS vi
LEFT OUTER JOIN (SELECT orderID,
SUM((qty * cost) + premium_charge) AS gptotal
FROM repair_partsID
GROUP BY orderID) AS a
ON a.orderID = vi.orderID
LEFT OUTER JOIN (...) AS b
ON b.orderID = vi.orderID
(etc)
WHERE vi.orderID = 79559

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are the man, Hugo. I bow to your majesty.sql

Help with SQL Group By please (results returned into / shown in C#.Net)!

Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).

Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOf

As i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:

"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...

Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)

Thanks in advance!

anyone?|||

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)

or this:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(ReviewRating/ReviewRatingOutOf)

If ReviewRating is an integer, and ReviewRatingOutOf is an integer, then you should cast one to a float like:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(CAST(ReviewRating AS float)/ReviewRatingOutOf)

otherwise you won't get what you expect because (int) / (int) will always return the floor of the result. So 1/2=0 1/8=0, 1/10=0, and the only way to score a non-zero result would be a perfect 5/5, 10/10, etc. By casting one to a float, then it the divide will return a float result. So (float)1/(int)2=(float)0.5

|||

Thanks for trying Motley - but all those methods give an 'overflow' error. I'm using Access 2003 by the way - just realised that may be important and that I hadn't mentioned it!

Any more help really appreciated!

|||Is there a record that ReviewRatingOutOf is 0?|||

Ahhh - yeah there is. Is there an easy way around that, or is it best to simply remove/change it? It's a review that didn't have an accompanying score (obviously!).

Thanks again for you help so far Motley.

|||

Think I got it working myself - here's my final code:

"SELECT ItemID FROM Reviews GROUP BY ItemID HAVING SUM(ReviewRatingOutOf) > 0 ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf) DESC"

That seems to give me exactly what I was after, with the highest-rated items arriving first. Unless you see something that'll cause problems, it's the perfect solution for me (that also allows for items with ReviewRatingOutOf = 0).

Thanks again for your help!

|||Just make sure that if both ReviewRating and ReviewRatingOutOf are both defined as an integer type (int,bigint,smallint,tinyint,bit) that you cast one or both to a float before the division or you'll get unexpected results.

Help with reading database.

I have this code that I hacked together from someone else's example. I kind of understand how it works. I just don't know if it will and i am not in a location right now to check. I was wondering if I did this correctly first, second how can it improve and should i do something different. Basically i just want to check the password in a database. I am passing the username and password to this function from another functio

private bool authUser(string UserName, string Password)
{
string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"];

SqlConnection DBConnection = new SqlConnection(connectionString);

bool result = false;

DBConnection.open()
SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection)
SqlDataReader checkDataReader = checkCommand.ExecuteReader();

if(checkDataReader.GetString(0) == Password)
{
result = true;
}
else
{
result = false;
}
checkDataReader.Close();
DBConnection.Close();

return result;
}

Thank you
Buddy LindseyNo. It won't work.
You're using the Password in your SQL statement, not the username. So unless the users are really silly and use their usernames as their passwords, it's going to fail.
More seriously, using string concatenation to build query strings represents one of the biggest security disasters on the planet. Never, ever do it. Just search the web for "SQL Injection attack" to see what I mean.
You should also ponder whether == with string comparisons is case sensitive or not.
You can also leak connection resources because you're not using try {} finally {} blocks or the C# using(){} wrapper.
And I'm really hoping that the password is protected in some way (by hashing or encryption), so that the password that is passed into this method is not the plain text password as entered by a user.
|||Yep that definetly won't work,
I agree with DMW, people who write code like that should be shot! :-)
At it's most basic that function may work , apart from the obvious mistake of querying the Password as the Username,
I also convert my string comparisons to .toupper or .tolower , just get rid of casing issues.
on Passwords I always enter them into the DB encrypted, and basically compare the encrypted text.
I would never do this in Code, as I would rather do this kind of operation in the Stored Proc, and let the SP deliver the result.
Inline SQL is so Passe, in a field where we must be moving with the times , this is a no no.
|||That is a little harsh of a response don't you think. Did youtake the time to consider that i may be a new developer and not reallyknow what i am doing.
Anyway, that is why I posted I wanted to dknow if i was doing it rightor not. And when i pass the password to the function it willalready be hashed.
Thanks for the help.
|||

appologies if I came across a little, harsh I did not certainly mean in it in that way,

I was trying to be funny.

I'll stick to code, because I suck at language :-)

|||I agree.
I don't recall advocating that ANY developer ever be shot.
And the reason that I spend my time on this forum is that I'm passionate about passing on what little experience I have to other developers so that they can learn from my mistakes.
So keep posting, and you'll get lot's of support and advice.
Speaking of which, I'll offer one other piece of advice: never, ever use someone else's code (especially not sample code or book code) unless you're really happy with how it works. Most samples, including book samples, don't follow best practice. If they did, the code would be harder to follow and generally much too long to fit in a book. A lot of "book code" (by which I mean conference samples, MSDN samples, my samples) is written to try and explain a learning point, not for commercial use. I know that this sounds daft, but that's the way it is.
The most common thing that is omitted is error handling code (which is critical), and the normal security reviews that would accompany real application development.|||Apology accpeted no hard feelings at all.
yeah one reason I posted was to make sure that I was doing it right. Thank for taking the time to answer.
I have the book code complete so that i can learn better ways of codingI guess i need to pull that sucker out and try to read it again.
Thanks for all the help.
sql

Help with query nulls and addition

Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:

Col A Col B Col C

Row1 1 1 2

Row2 2 3 5

Here is the query

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint, Testint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

insert

into @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

SELECT

player_name,min(BuyIn)as BuyIn,min(TopUp)as TopUps,min(ReBuy)as ReBuy,min(Winnings)as Winnings,min(Events)as Events,min(test)as test

FROM

@.t

GROUP

BY player_name

ORDER

BY BuyInDESC

--ORDER BY TOPUPS DESC

END

THis is where I attempt to add the coloms but I get a null result

insertinto @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

any help would be great.

You could us the ISNULL fucntion:

insertinto @.t(player_name, test)

select

player_name,(ISNULL(TopUp, 0)+ ISNULL(Rebuy, 0))as Test

from

@.t|||

Hi the is null removes the nulls but I am still unable to add the coloums together, instead of null I get 0 in the test col. I am able to add topup +topup or Buyin + Buyin and I get the result but when I try to add the different cols its null or 0 if I use your suggestion.

any idea?

|||How about using COALESCE instead of ISNULL?|||

Hi. I hav two ideas about that.

1. Declare the column 'test' as a calculated column:

declare @.table TABLE
(player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test AS (TopUp + ReBuy))

2. If you, for example SELECT the table for one player use this: "SELECT * FROM table WHERE player_name = 'player1'" and get somethiong like this:

player_name TopUp ReBuy

player1 5 NULL

player1 NULL 3

So, if you sum each row its equal to TopUp + NULL and ReBUy + NULL.

May be you need in the final select this:

SELECT (TopUP + ReBuy) FROM

(SELECT SUM(TopUP) as TopUp, SUM(ReBuy) as ReBuy FROM @.t) As t

or the other option could be to insert the first time, an after that update the rows for the player.

|||

This is most excellent, thanking you exactly what I was looking for: here is my working query with your suggestion. Thanks again a great help!!!

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

--insert into @.t (player_name, test)

--select

--player_name, (ISNULL(TopUp, 0) + ISNULL(Rebuy, 0)) as Test

--from @.t

Select

*,(TopUps+ ReBuy+ BuyIn)as Cost,(winnings-(TopUps+ ReBuy+ BuyIn))as Profit

FROM

(SELECT player_name,(ISNULL(min(BuyIn),0))as BuyIn,(ISNULL(min(TopUp),0))as TopUps,(ISNULL(min(ReBuy),0))as ReBuy,min(Winnings)as Winnings,min(Events)as Events

FROM

@.tGROUPBY player_name)as t

GROUP

BY player_name, buyin,topUps, Rebuy, winnings, events

Order

by Profitdesc

Help with query

I'm not sure if this is the right forum but here goes:

I want to make a query that selects data from multiple tables and joins it all together. I have that but what i want to do is only select the data I need. at the moment it is returning columns that are not necessary. I'm trying to do something like this:

Code Snippet

SELECT
sysdba.OPPORTUNITY.OPPORTUNITYID AS OPPID
FROM sysdba.OPPORTUNITY
INNER JOIN sysdba.C_OPPTYINFO
ON sysdba.OPPORTUNITY.OPPORTUNITYID = sysdba.C_OPPTYINFO.OPPORTUNITYID

For some reason the Inner Join is not joining the two tables. Any one have any suggestions?

Thanks in advance for the help.

Nothing wrong with your query, the tables are properly joined -IF both have a column [OpportunityID].

However, are you sure that there is data in both tables with the exact same [OpportunityID]?

|||The limited information you gave makes it hard for us to answer your questions: Perhaps the OppurtunityId is not the only key needed to identify the matching rows in both tables ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with nested inner joins

Hi,
I want to find some people in my SQLServer 2000 database. It's a quite large
database, with approx 200 tables.
Together with the person, I want some information attached to him. However,
this information is in another table that can be reached via some other
tables.
My question is:
How do I most efficiently extract this information? Is inner joins a good
option or is there a better way. If I need information from table1 and table
5, is this a good idea?
SELECT table1.ID, table5.info
FROM table1
INNER JOIN table2 ON table1.xxx = table2.xxx
INNER JOIN table3 ON table2.xxx = table3.xxx
INNER JOIN table4 ON table3.xxx = table4.xxx
INNER JOIN table5 ON table4.xxx = table5.xxx
Thanks,
Mats-LennartWithout seeing DDL, I can only go on assumptions...
I am assuming that the only logical way to connect tabel1 to table5 is via
tables 2, 3, and 4. Based on this, I believe the SQL below is the only way
to get the data you want.
If you post DDL (table creates, primary and foreign keys) for the tables
involved, folks may be able to explain another way to do it, or possibly
changes to your database structure.
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:e1rQtquNGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to find some people in my SQLServer 2000 database. It's a quite
large
> database, with approx 200 tables.
> Together with the person, I want some information attached to him.
However,
> this information is in another table that can be reached via some other
> tables.
> My question is:
> How do I most efficiently extract this information? Is inner joins a good
> option or is there a better way. If I need information from table1 and
table
> 5, is this a good idea?
> SELECT table1.ID, table5.info
> FROM table1
> INNER JOIN table2 ON table1.xxx = table2.xxx
> INNER JOIN table3 ON table2.xxx = table3.xxx
> INNER JOIN table4 ON table3.xxx = table4.xxx
> INNER JOIN table5 ON table4.xxx = table5.xxx
> Thanks,
> Mats-Lennart
>