Help with totalling columns in SQL select statement

Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work! 

I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order.
I must be doing something wrong, can anyone help?

SELECT sum(Order_Item.Quantity) as Items,
sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total
FROM [Order]
JOIN Order_Item ON Order_Item.[OrderID] = [Order].[ID]
GROUP BY [Order].[ID],
[Order].OrderRef,
[Order].CustomerID,
[Order].ReceivedDate,
[Order].Postage,
[Order].Discount
ORDER BY [Order].ReceivedDate DESC
Items Total
==== =====
1 14.9900
1 13.5920
3 33.6750
2 27.1840
5 67.9600
0
RayBanUk
2/18/2005 3:58:12 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
554 Views

Similar Articles

[PageSpeed] 18

I'm not sure what are you trying to group by (productid, customerID,etc.)?  Here's an example of getting the items and total using northwind database:

SELECT od.productid, count(od.quantity) as Items, sum(od.unitprice*od.quantity-od.unitprice*od.discount) as Total
from orders o join [order details] od on o.orderid = od.orderid
Group by productid
0
lano
2/18/2005 10:37:28 PM
You're problem is the group by. If you just need a total of all of the items, take out the group by statements.
http://www.rocksthoughts.com
http://www.jumpstarttv.com - Free Technical Videos!
0
chrisrock
2/19/2005 7:34:57 PM
Thanks ChrisRock! I took out the group by AND the order by and it gave me what I wanted. Told you my brain wasnt working on Friday. Lan Ong, thanks also for looking into this for me, but I now know I shouldnt have used group by at all!
0
RayBanUK
2/21/2005 10:43:23 AM
Reply:

Similar Artilces:

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

Sql Datasource select statement
I have a gridview that uses a sqldatasource to display (classid, userid, startdate, enddate) from a class table.  My datagrid also contains a submit button.  When this button is clicked I am inserting this data into a new table called register.  I need a way to not show classes for the user if that user is already in the register table for that class.  Currently all items are being shown which would allow the user to register for the same class more than once.  Any ideas?   Thanks  you could make classid and userid as a pri...

help with a sql select statement
Below is my Select statement.  Its not working correctly as its returning no values when it should be returning values.  I have a table with Counseling Sessions that is tied to a table with site names.  There is a session variable in my app called "AhecLoc", this coincides with a field in the SiteNames table.  What I want to do is return only Counseling Sessions that take place at those sites that coincide with the user's session variable "AhecLoc".  Please advise me as to what is wrong.  I'm using SqlServer 2005 and Visual Stu...

Help with SQL SELECT statement
Hi,I have two tables, lets say TableA and TableBTable A is like thisstockNumber   Desc            Glasscode2205          example desc    0102152206          example 2       0102162207          example 3       010217Table B is like thisglasscode   vehicle010215    &nbs...

Please Help me:SQL statement failed: (-301) Internal database error ??? -- transaction rolled back SQL statement failed: (-101) Not connected to a database
Hi all, I am using Adaptive Server Anywhere 8 version 8.0.2.4239. I am able to sync successfully between 2 desktops with the same version. When I am trying to sync with my pocket pc device version 8.0.4122, Towards the end of Synchronization (nearly 95% completed) ,I am getting this error "E. 05/29 19:28:31. SQL statement failed: (-301) Internal database error ??? -- transaction rolled back E. 05/29 19:28:31. SQL statement failed: (-101) Not connected to a database" The log file contains E. 05/29 19:28:31. SQL statement failed: (-301) Internal database er...

Need help with my SQL SELECT statement
I have the following tables (with important fields) that all need to be joined onto my Orders table: Orders - PreNotesID, PostNotesID, StatusID, ReportID, PropertyTypeIDOrderNotes - NotesID, NotesDescStatus - StatusID, StatusDescOrderTypes - ReportID, ReportDesc, PriceIDPriceCodes - PriceID, PriceDescPropertyTypes - PropertyTypeID, PropertyTypeDesc Is the following SELECT Statement going to do what I need? The main thing that I am not sure about how to Join PriceCodes ON orderTypes then join both of them ON Orders "SELECT * FROM Orders As o JOIN OrderNotes As on ON o...

sql select statement help needed.
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path. I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belong...

Beginners help with Sql Select Statement.
Hi one and all. I apologise in advance for this question, I have searched and searched and have almost gone boss eyed looking at example after example that make no sense to me.I've been tasked with sorting out effectively a SQL frontend. I got Visual Web Developer express and set about trying to learn "on the fly" but have come unstuck quickly!!! The main crux of the headache is SQL Select statements. The side pane of the site has a number of different "searches" avaliable to the user (radio button list). There is a standard search whereby it returns all fro...

SQL server 2005 select statement help!
I have a sql server 2005 and need to run a select statement that I'm having trouble with. I want to capture the first 4 rows with columns content_id (int) and event_start_time (datetime) , but I want to capture only unique content_id's (some will repeat). For instance, right now I'm getting something like this:content_id     event_start_time22                  3/20/2009 12:00:00 AM 38                  3/20/2009 9:30:00 AM38&nbs...

Need help with Select distinct SQL statement
On the SQL statement below, I am trying to get the d.group_descr distinct because there are duplicates for this field but when I run the query in SQL it gives me dups. Does anyone have any ideas on  how to write this statement to get just the d.group_descr to show distinct but not the other fields in the query. SELECT distinct d.group_descr, a.first_name, a.last_name, a.user_id, b.email, d.group_id FROM icc_user_data AS a LEFT OUTER JOIN icc_users AS b ON a.user_id = b.user_id LEFT OUTER JOIN icc_users_groups AS c ON a.user_id = c.user_id LEFT OUTER JOIN icc_groups AS d ON c.group_id =...

2 sql select statements in 1 sql string just wont work!
hi there, i have a bit of a strange problem, and i must be doing something wrong because this works with insert and update statements in other pages. What is going on is that i am trying to do 2 select statements in the same bit of code so i dont have to open a datareader twice, but if i put the delivery charge select before the other it seems to get it, but if i put it after it does not, i just cant work it out! i have posted my code below for you all to have a look at because im stumped! //gets order id and customer id and puts them to strings and int to be used laterstring strCustID = Re...

SQL Logging SQL Statement
Hi at All!! I use ASA 7 so I'd like to know if there is a way to logging the sql instruction that database server dbsrv7, receive from clients ? I need this because a client that use odbc connection after performing an insert into a table, the client receive the Message: "SQL truncate". I need to know the field on the table that generate this warning!! Thanks in advance Massimo The tool that does this is called "request-level logging" and in V7 you have to enable it from the command line when you start the server, using the -zr (and optionally -zo) c...

Need to change SQL select statement
I get this error when I run the following select statement. Can anyone help?  sql = "select * , LastName + ' ' + FirstName as [athletename], Cast(thours + ':' + tminutes + ':' + tseconds + '.' + ttenths as varchar(50)) as [timerowed] from [trcjrs].[guest].qryErgwos where piecetype = '6k' order by thours, tminutes, tseconds, ttenths" Conversion failed when converting the varchar value ':' to data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trac...

Need help with SQL Statement (combined SELECT and WHERE at same time)
  I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name.  Also, "Total Number of files" and "Category Description" under each "Main Category". Sample: Human Resources All documents related to new hire process. New Hire Request forms, New Hire forms, Termination Forms Total Files:  25   Programs & U...

Web resources about - Help with totalling columns in SQL select statement - asp.net.sql-datasource

Volkswagen could face fines, totalling tens of millions, in Australia
Volkswagen is facing fines potentially totalling billions of dollars if it used cheating software to rig pollution tests on cars sold in Australia. ...

Kern and Santa Barbara Solar Projects Totalling 72 MW Will Help California Meet Renewable Energy Goals ...
PG&E is to purchase 72 MW of electricity from two photovoltaic solar power plants which are being constructed in Central California. One of ...

Menlo Park employees may get two raises totalling 5 percent in next month
Menlo Park's 134 non-sworn government employees will receive a couple of pay raises totalling 5 percent in the next month if the City Council ...

Clearwater minister accused of aiding tax refund scams totalling $755,900
TAMPA — The pastor of a small Clearwater church admits in a federal plea agreement to cashing $755,900 in tax refund checks that were obtained ...

Lazada raises $250 million, totalling $486 million so far
Enricko Lukman / Tech in Asia : Lazada raises $250 million, totalling $486 million so far — Rocket Internet's e-commerce ventures are on a roll. ...

Only Four Teens Showed Up To Help Clean Holloway’s Home – Damages Now Totalling $40,000 From Illegal ...
NEW YORK - The former NFL player whose home was broken into and trashed by some 300 partying teens held a picnic for veterans Saturday and to ...

Microsoft employees raised $100.5m for charity in 2011, totalling $946m since 1983
Microsoft has announced that its employee giving campaign helped to raise $100.5 million last year, donating money to more than 18,000 community ...

Insurance rebates totalling $1.3 billion could be on the way this summer, study says
This summer millions of Americans could find a check in the mail from an unexpected source: their health-insurance company. Consumers and businesses ...

Obesity costs the global economy as much as war and terrorism, totalling $2TRILLION a year
Researchers at the McKinsey Global Institute have warned if the current prevalence of obesity continues at current rates, almost half the world's ...

Pamela Anderson hit with double whammy of tax bills totalling £230k - The Sun - Showbiz
US tax inspectors claim Pamela Anderson owes total of £230K in unpaid taxes

Resources last updated: 1/22/2016 5:58:12 PM