Skip to main content

SQL - Calculated and Concatenated Fields

When bringing fields together, there are two types:

  • Numeric Calculations
  • Alpha-Numeric concatenations

In the JOURNAL file, you see two fields: DEBIT and CREDIT. If you want to list transactions which have occurred, you can use two columns and list both the debit and credit, or you can list one column which is the net result of the two columns. This makes it easy to have an accurate total at the bottom.

You can do this easily with a simple calculation.

SELECT JOURNAL.TRANNUMBER,JOURNAL.TRANADATE,JOURNAL.DEBIT-JOURNAL.CREDIT as 'Total'

FROM JOURNAL

In this case, the debit and credit columns are subtracted and the net result appears. You can use + (plus), - (subtract), * (multiply), or divide (/). Just be sure you never divide by zero, as that would cause an error as you can never divide anything by zero.

The second type is when you want to bring two or more fields together. You can do that by using the CONCAT function. This allows you to Concatenate two or more fields.

Let's say you wanted labels and you wanted both the First and Last names together, and you also wanted the City, State and ZIP formatted nicely.

SELECT CONCAT(FIRSTNAME,' ',LASTNAME) AS 'NAME', STREET AS 'ADDRESS1', CONCAT(CITY,', ', STATE, ' ', ZIPCODE) AS 'ADDRESS2' FROM RESERVE

In the above example, we concatenated the First name, plus a space, plus the last name to form something like "John Jones".

In the second part, we concatenated the City, plus a comma, plus the state, plus a space, plus the zipcode to form something like "Tampa, FL 33607"

It's important that when you concatenate fields, you pay close attention to the single quotes being used to form a literal such as:

', ' which means a comma and one space

or

' ' which means one space

The quotes are required for literal fields and everything inside the single quotes is placed in the result.

You do not, however, single quote a field name such as "LASTNAME" or "CITY", just the literals needed, if any.