How do you create a total field/column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How do you create a total field/column?

Author

Topic

BadBoy House
Starting Member

41 Posts

Posted - 2011-01-21 : 11:13:26

I'm working on a query that needs to add up the figures in a particular column.

I've pasted a copy of the query below. I need to create a field/column that shows the grand total of the OUTSTANDING column. Later I will need to add totals to the TOTAL and ALLOCATED columns.

thanks in advance.


SELECT

	tblClient.ClientRef AS ClientRef,
	CASE
	WHEN ISNULL(tblClient.FirstName,'') = ''
		THEN tblClient.SearchName
		ELSE tblClient.FirstName + ' ' + tblClient.SearchName
	END AS [Client Name],

	tblNominal.NomTypeID as 'NomTypeID',
	tblNominal.Date as 'Date',
	tblNominal.Ref as 'Bill Number',
	tblNominal.Total as 'Total',
	tblNominal.Allocated as 'Allocated',
	
	tblNominal.Total - tblNominal.Allocated as 'Outstanding'


FROM
	tblNominal LEFT OUTER JOIN tblClient ON tblClient.ClientID = tblNominal.ClientID

WHERE

	tblNominal.NomTypeID < 10 and
	tblNominal.Total <> tblNominal.Allocated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-22 : 08:33:03

[code]
SELECT

tblClient.ClientRef AS ClientRef,
CASE
WHEN ISNULL(tblClient.FirstName,'') = ''
THEN tblClient.SearchName
ELSE tblClient.FirstName + ' ' + tblClient.SearchName
END AS [Client Name],

tblNominal.NomTypeID as 'NomTypeID',
tblNominal.Date as 'Date',
tblNominal.Ref as 'Bill Number',
tblNominal.Total as 'Total',
tblNominal.Allocated as 'Allocated',

tblNominal.Total - tblNominal.Allocated as 'Outstanding',
sum(tblNominal.Total - tblNominal.Allocated) over () as 'Grand Outstanding'

FROM
tblNominal LEFT OUTER JOIN tblClient ON tblClient.ClientID = tblNominal.ClientID

WHERE

tblNominal.NomTypeID < 10 and
tblNominal.Total <> tblNominal.Allocated
[/code]

KH
[spoiler]Time is always against us[/spoiler]

BadBoy House
Starting Member

41 Posts

Posted - 2011-01-24 : 04:26:23

Thanks. That's almost it.

It's now totaling the column but I need the total for each group of entries.

Ideally I would like to have one line for each client, with their respective total.

For example,

If the query is set to pick out just one client, this query works really well.

However if the are say 2 lines for client 1, and 3 lines for client 2, the Grand Outstanding column totals the entire Outstanding column.

Is there a way of calculating and showing the total of the entries for client one, then showing the total of the entries for client 2?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-24 : 14:31:30

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

You need to read a book on Data Modeling, so you will stop putting that silly "tbl-" on table names, learn the huge differences between fields and columns and how to name data element. For example, you just said you have only one client! I will try to clean it up for you and give you a quick SQL lesson:

SELECT C.client_ref, C.first_name, C.search_name
N.nom_type, N.screwup_date, N.ref, N.refs_total,
N.allocated_something,
-- get rid of this next computation
(N.refs_total - N.allocated_something)
AS outstanding_something
FROM Nominal_Somethings AS N
LEFT OUTER JOIN
Clients AS C
ON C.client_id = N.client_id
WHERE N.nom_type < 10
AND (N.refs_total
- N.allocated_something) <> 0;

1. DATE is a reserved word in SQL, not a data element name -- date of what?
2. An attribute can be a "<something>_type" or a "<something>_id" but some weird meta-data hybrid type_id.
3. We never format data for display in the query; in any tiered architecture, that is done in the front end. This more basic than SQL.
4. Why alias columns to their own names? Or to new names? The idea of a data model is that the names are good everywhere in the system.
5. Why did you think that the "outstanding_something" alias would be available? Do you know how a SELECT statement works?

>> I need to create a column that shows the grand total of the "outstanding_something" column. Later I will need to add totals to the "refs_total" and "allocated_something" columns. <<

What are you grouping by? Based on your narrative, you can just write:

SELECT SUM (N.refs_total) AS refs_grand_tot,
SUM (N.allocated_something)AS allocated_something_grand_tot
FROM Nominal_Somethings AS N
WHERE N.nom_type < 10
AND (N.refs_total
- N.allocated_something) <> 0;

Want to try again? Before you post, however, look up the GROUP BY.. ROLLUP option

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources