<

SQL 101 - Calculated Fields

Published on
8,949 Points
2,949 Views
Last Modified:
Approved
Community Pick
When working with SQL you will inevitably be asked to return data from your database in a way it's not stored.  You'll be asked to return city, state and ZIP as a single string, yet they are stored separately.  You'll be asked to return grand totals, but you only have line item totals.  You'll be asked to provide counts, totals, or averages, and none of that is in the database.

The answer to all these scenarios is to calculate those values for the user.


Concatenating Fields

Let's say we have an address table, and the columns city, state, and zip are each stored in there as VARCHAR.  It's important that all three are varchar, otherwise you'll get errors when you try to combine them all into one string.  Given this table, you want to present a list of
    cityName, state zipcode
(Note the spacing and the comma in the string)   How would we do this?
SELECT
   city + ', ' + state + ' ' + zip AS [output]
FROM address

Open in new window

output
------
Charlotte, NC 28222
Kannapolis, NC 28081
Beverly Hills, CA 90210

Open in new window


Concatenating VARCHAR values is simple.  Just remember all the fields have to be VARCHARs before concatenation.  If one or more fields aren't, then you can CONVERT them to VARCHAR first.


Mathematics

Let's say you have a table of sales, and in that table it lists, productName, quantitySold, and price.  How could you query this table to add a subtotal column that shows the price paid for that quantity sold?
SELECT
     productName
   , quantitySold
   , price
   , quantitySold * price AS subTotal
FROM sales

Open in new window

prodctName   quantitySold   price   subtotal
----------   ------------   -----   --------
pen          1              .99     .99
paper        25             1.00    25.00

Open in new window

You can use any mathematical operator (not just multiplication).  Basically any time you compute a column, the query will look like a formula, keep that in mind, and this will be a breeze!


Conclusion

This is only the beginning of creating calculated fields in SQL.  In later articles, I'll show you extra functions that can do far more than simple math.  After that I'll show you aggregate functions that can sum several rows of information into one row.

If you have any questions, please send them in, I'm working hard to help explain the fundamentals of SQL so you can become better equipped to work through the many questions you'll be asked one day.  I can only help you, if you "help me, help you."
0
Comment
0 Comments

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month