Ask Dr. Dotnetsky - sql query
Asked By shipra
02-Jun-10 06:42 AM
my db is on hostel management this is my query its nt working prop.
select sum((btea*rate +bsandwitch*rate)) from diet,menu where item_name = 'btea' and item_name = 'sandwitch';
my prob. is oracle cant decide wat rate it shud take..... we r nt gettting th ans dere is sum prob. in r query....
hw we multiply in sum function please tel
Santhosh N replied to shipra
If you have rate as one of the column and if its type is numeric then you should get the query working and also if both the btea and bsandwitch are also numeric types and available columns..
Check if you have them as numeric and available in the tables you selected in the query
Sagar P replied to shipra
Try this query and check ;
select sum(btea*rate) + sum(bsandwitch*rate) from diet,menu where item_name = 'btea' and item_name = 'sandwitch';
Sara J replied to shipra
You can't have tea and sandwich in the same row in the column item_name
You should have a rate column separately or from some other table, then try the following query
select sum((item_name*rate)) from diet,menu where item_name = 'btea' or
item_name = 'sandwitch';
Anoop S replied to shipra
select sum((btea*rate +bsandwitch*rate)) actually what are btea and bsandwitch, i hope this are some items in the column, thats why you provided in the where condition so change btea and bsandwitch with your column name in the select query like
select sum((Colname_of_Btea*rate +Colname_of_bsandwitch*rate))
Devil Scorpio replied to shipra
Hi Shipra,
- Suppose we have a table "tblRate" having following fields & datas
Item_Name Rate
------------- -----
btea 7
bsandwich 20
bicecream 30
- Use following Query named "QryItemName" to find the corresponding rate for "btea" & "bsandwich"
SELECT tblRate.Item_Name, tblRate.Rate
FROM tblRate
GROUP BY tblRate.Item_Name, tblRate.Rate
HAVING (((tblRate.Item_Name) In ("btea","bsandwich")));
- Use the following Query name "QryRate" to find the the total price of tea & sandwich
SELECT SUM(QryItemName.Rate) AS Price
FROM QryItemName;
Best Regards :)
Getting a "SUM' is not a recognized built-in function name." error. I am trying to create a that will contain the total of the values in a column I created in the select statement. I know it will repeat the same value over and over again for each column, but that doesn't matter. I simply need to value in my web application. SELECT BuID, AuditArea, DeadlineDate, CASE WHEN DeadlineDate < GetDate() Then DateDiff(d, DeadlineDate, GETDATE()) Else '0' END AS DaysOverDue, SUM ( CASE WHEN DeadlineDate < GetDate() Then DateDiff(d, DeadlineDate, GETDATE()) Else '0' END AS DaysOverDue) AS 0 Any help will be greatly appreciated Kind Regards Marilee Have you tried executing the query in sql server, there is some syntax error which you need to correct: SELECT BuID, AuditArea, DeadlineDate, CASE WHEN DeadlineDate < GetDate() Then DateDiff(d, DeadlineDate, GETDATE()) Else '0' END AS DaysOverDue, SUM ( CASE WHEN DeadlineDate < GetDate() Then DateDiff(d, DeadlineDate, GETDATE()) Else '0' END AS DaysOverDue ) AS
sql function HAI, How to create function in sql. And how to use that in our sql query. if it returns any value how to get that value in sql. Thanks in advance HI syntax -Transact-SQL Scalar Function Syntax CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ , . . .n ] ] ) RETURNS CREATE FUNCTION whichContinent (@Country nvarchar(15)) RETURNS varchar(30) AS BEGIN declare @Return varchar(30) select @return = case @Country when 'Argentina' then 'South America' when 'Belgium' then 'Europe' when 'Brazil' then end return @return end And you can use this function as; print dbo.WhichContinent('USA') select dbo.WhichContinent(Customers.Country), customers.* from customers http: / / www.sqlteam.com / article / user-defined-functions
Help modifying SUM Table Structure DCR - -- -- - CustomerNumber CC PDC EnteredDate FeeGoal FeeSchedule PostedAmount I need a query which will do: SUM(PostedAmount + SUM(All PDC where EnteredDate in current month) + SUM(All CC where entered date in current month) + SUM(All PDC where EnteredDate not in current month SUM(All CC where entered date in current month)) * FeeSchedule for each company call that In-House for the above Sum Then I need to do a grand total on all In-House as one sum
SumColumn: Custom DataGrid Column that automatically shows Sum / Count / Average in DataGrid Footer. A normal requirement in any ASP.Net application is to sum / average up the values in a DataGrid column to the Footer. In this article we to avoid repetition of the same code. Title : SumColumn: Custom DataGrid Column that automatically shows Sum / Count / Average in DataGrid Footer. Author: Syed Aziz ur Rahman Environment: DotNet 1 .x, NT 4 . 0 , Win95 / 98 / XP / 2003 Keywords: Sum Average DataGrid Column Custom DataGridColumn ASP.Net Level: Intermediate Section DotNet Sub-Section ASP.Net demo project - 6 Kb • Download source - 2 Kb The output using SumColumn Contents • Introduction • Background • Query database for the sum • Use DataTable Compute() Method • Use DataGrid ItemDataBound Event • Problem • SumColumn for the rescue • Introduction to Note • References • Revision History Introduction A normal requirement in any ASP.Net application is to sum / average up the values in a DataGrid column to the Footer . In this article we DataGrid showing the list of employees. Columns are Name, Salary and Bonus. Normal solutions are • Query database for the sum: One way is to compute the sum of the data through
SQL Server Reporting Services SQL Server Reporting Services - Lessons Learned In Development and Deployment By Bill Jones, Jr. Printer Friendly who follows will have to learn these items for themselves. Common Reporting Services Deployment Diagram SQL Server 2000 Reporting Services relies on several major components to function correctly. This diagram shows particular reports through the application user interfaces. For example, in our application, the client can select a particular report entry form a list, click on a link and the report can Services. The class is application independent. IIS, Reporting Services and Database Server: This machine hosts SQL Reporting Services and SQL Server 2000. The Reporting Services web service and the Report Manager Web application both run the web service provider is a "web.config" setting. Database Server: This machine hosts the SQL Server 2000 databases required to persist application information. Reports can be drawn from SQL Server, ODBC or Oracle data sources. Development Workstation: This represents the programmer's workstation. At