Tutorial: Using SQL Queries in wMobile Desktop (Part 2: A Sequel to SQL)

by Fred Block on January 2, 2015

We hope that in part 1 of this SQL blog series you completely immersed yourself into SQL or at least became acquainted with it and are eager to learn more. In this "Part 2: Sequel to SQL" we'll cover getting unique (i.e., DISTINCT) values form the database, using some Boolean operators to enhance filtering options in all of your queries, and returning groupings of data (extremely useful). This all will expand on what was learned in the prior blog article, "Using SQL Queries in wMobile Desktop". If you have not read that article, you may want to before diving into this one.

For some of our examples in this "Part 2" article we'll use this table of data:

CONTACT1

Contact

City

State

Zip

Contact_Type

Truman Feichtner

Bloomfield

NJ

07003

Customer

Yvonne Tjepkema

Fairfield

NJ

07004

Customer

Nu Mcnease

Harrison

NJ

07029

Customer

Tasia Andreason

Kearny

NJ

07032

Customer

Lili Paskin

Kearny

NJ

07032

Customer

Shonda Greenbush

Plainfield

NJ

07062

Customer

Cecil Lapage

Plainfield

NJ

07062

Prospect

Erinn Canlas

Cherry Hill

NJ

08002

Customer

Lizette Stem

Cherry Hill

NJ

08002

Prospect

Alpha Palaia

Cherry Hill

NJ

08002

Customer

Art Venere

Bridgeport

NJ

08014

Customer

Willow Kusko

New York

NY

10011

Customer

Jose Stockham

New York

NY

10011

Prospect

Layla Springe

New York

NY

10011

Customer

Haydee Denooyer

New York

NY

10016

Prospect

Jess Chaffins

New York

NY

10016

Customer

Alishia Sergi

New York

NY

10025

Prospect

Fausto Agramonte

New York

NY

10038

Customer

Justine Mugnolo

New York

NY

10048

Customer


Using DISTINCT with your SELECT statement

Often enough you may need to return data from your tables that has only distinct (i.e., unique) values. As an example, consider a field that stores Contact Types. Your database may have thousands upon thousands of contacts and you need to know what each distinct contact type is contained in that column from all of those rows. Enter "DISTINCT"!

select distinct Contact_Type from contact1

order by Contact_Type


The results have one of each distinct value found in the column searched in (Contact Type in this case). Since there were only the two contact types in our example table, only those two were returned. NOTE: Had we queried for distinct "states", we would have had only NJ and NY returned.

Contact_Type

Customer

Prospect


One more query using DISTINCT in the SELECT statement: In this query we are using two columns in the SELECT statement. Now the results will include rows where "state" plus "contact_type" are distinct when combined and then compared to other rows.

select distinct state, Contact_Type from contact1

order by Contact_Type


Here are the results. More columns may be added to this type of query as required.

State

Contact_Type

NJ

Customer

NY

Customer

NJ

Prospect

NY

Prospect

 

Boolean Operators for Filtering (AND | OR)

Filtering the data you want to have returned from the database is as important as breathing. Without filtering the data you want returned you'll get a fire hose of everything there is and that's not typically what you are after. In the last article we touched on the WHERE clause and using it to limit data based on specific criteria using: where state = 'NY', or using: where contact like '%red%'. Used singly, they have their own limitations. However, used in combinations there are more powerful solutions available to you. Let's start with an example: You want to return a dataset for a future mailing that contains contacts in the state of New York and the contacts must be within a specific zip code. Here we need to have our query filter using two different columns: STATE and ZIP, and only return rows where "both" filter conditions are true for each contact.

The following query contains two filters: the "state" column and the "zip" column. We are using the "AND" condition here (more on this later) because we only want to return contacts that have state = 'NJ' and zip = '07032'.

select contact, city, state, zip from contact1

where state = 'NJ' and zip = '07032'

order by contact


In these results we only see the two rows meeting both criteria: state is 'NJ' and zip is '07032' for each contact. (NOTE: The records were returned in order by contact due to the ORDER BY clause.)

CONTACT1

Contact

City

State

Zip

Contact_Type

Lili Paskin

Kearny

NJ

07032

Customer

Tasia Andreason

Kearny

NJ

07032

Customer


This time we will use the "OR" condition in a query. The following query contains filtering against the "zip" column only but in this example we're looking to return contacts that have either of two different ZIP codes ('10011' or '10016').

select contact, city, state, zip from contact1

where zip = '10011' or zip = '10016'

order by zip


In the results we only see the rows having Zip codes of '10011' or '10016'.

CONTACT1

Contact

City

State

Zip

Contact_Type

Willow Kusko

New York

NY

10011

Customer

Jose Stockham

New York

NY

10011

Prospect

Layla Springe

New York

NY

10011

Customer

Haydee Denooyer

New York

NY

10016

Prospect

Jess Chaffins

New York

NY

10016

Customer

Before we dive deeper into a query that mixes the AND with the OR conditions in a single query we need to discuss Boolean logic briefly. Boolean logic works with values that are either "True" or "False". We need to understand how these are related to each other as operations in a SQL query.

 

The AND Operator

For the "AND" operator, we take to values (we'll call A and B), each of which can be either "True" or "False". We "AND" them together to get a single "True" or "False" result. In this table, we see the "A" values and the "B" values along with the results:

A Value

B Value

Result

True

True

True

True

False

False

False

True

False

False

False

False


When using "AND", both values must be "True" to get a True result. In a query using "AND", both values must return a true condition for the row to be returned. Above we used: state = 'NJ' and zip = '07032' meaning that "both" state "AND" zip had to return "True" values in a given table row for that row to be returned.

 

The OR Operator

For the "OR" operator, we take to values (we'll also call A and B), each of which can be either "True" or "False". We "OR" them together to get a single "True" or "False" result. In this table, we see the "A" values and the "B" values along with the results:

A Value

B Value

Result

True

True

True

True

False

True

False

True

True

False

False

False


When using "OR", either of the two values must be "True" to get a True result. In a query using "OR", only one value must return a true condition for the row to be returned. Above we used: zip = '10011' or zip = '10016' meaning that "either" zip being '10011' OR zip being '10016' had to return "True" values in a given table row for that row to be returned.
 

This may be confusing so let's throw one out there that will never work (i.e., does not make sense to use) in the hopes of making this easier to understand. When you're asked to write a query to get all contacts from NJ and NY you may be tempted to write this because it "sounds" correct:

select contact, city, state, zip from contact1

where state = 'NJ' and state = 'NY'


One might think this would return all contacts that have a NJ and NY as their state but it will not and here is why. Each row in your database table is looked at by your query one at a time. The "AND" operator here is wrong because one row cannot have both NJ "and" NY as the state and therefore your query would not return any results. For this to work you need to use the "OR" operator. The way we "speak" and the way data is returned is not always the same so this is vital to understand or you'll get frustrated when you're not getting the results you expect.


Hoping this all makes sense, now we'll combine "AND" and "OR" in one more query before moving on. Please consider that your marketing department asked you for a list of all contacts that are "Prospects" residing in NJ and NY. NOTE: For this exercise, suppose our example database table contains more rows than you see and there are contacts from all 50 states contained in it. Keep that in mind... Here's our query:

select contact, city, state, zip, Contact_Type from contact1

where (state = 'NJ' or state = 'NY') and Contact_Type = 'Prospect'

order by zip


In our results we only see the rows having states in NJ or NY and Contact Type = Prospect.

CONTACT1

Contact

City

State

Zip

Contact_Type

Cecil Lapage

Plainfield

NJ

07062

Prospect

Lizette Stem

Cherry Hill

NJ

08002

Prospect

Jose Stockham

New York

NY

10011

Prospect

Haydee Denooyer

New York

NY

10016

Prospect

Alishia Sergi

New York

NY

10025

Prospect


Please note that we used parentheses around the state filtering and that alone would return a True/False condition (we used OR in that condition). For each row, if state was NJ "or" state was NY, that condition evaluated to "True". This result (from the state filter) is AND'ed with the Contact Type filter for the row as well. When Contact_Type = "Prospect", that filter returned "True". Since both filters return "True", we have "True" AND "True" and the row is returned to us. If either the state filter or the Contact Type filter returned "False" we would not get that row in our results and is why we do not have rows that are "Customers" or in a state other than NJ and NY.

 

Grouping Results (GROUP BY clause)

There will be occasions when grouping data in the results is necessary and actually the "best" way to look at data a specific way. Suppose we want to know how many contacts are in each state within our database. There is no need to count them because we can use the GROUP BY clause. You may also want to find the highest product sale amount, the lowest fee paid for your last email campaign, or the newest sales in a certain month. With some practice the GROUP BY clause will help you get these results. We're going to keep it simple though for now and start with getting counts.

Let's get counts from our table of contacts within each state. Here's that query using COUNT. Please note that the data returned will not match that of our example table above.

select state, COUNT(*) [count] from contact1

group by state

order by count(*) desc


In our query you may have noticed that we have "[count]" after "COUNT(*)". The text in the square braces is an alias (i.e., title) for the column. Without setting this alias, SQL Server will name the column: "(No column name)" and that just isn't too helpful. This alias tip can be used after any column name in your select statement. Back to grouping: Only partial results are shown here but you can see that the state is shown and the number of rows in the table that exist for the state. This means that there are 52 contacts in NJ, 32 in TX, only 1 each in AR and IA, etc. Notice the ORDER BY is sorting largest to smallest number of contacts per state.

state

count

CA

72

NJ

52

NY

46

TX

32

AR

1

IA

1


We could take this a step further and write the query to return only states that have more than 50 contacts. Here is how:

select state, COUNT(*) [count] from contact1

group by state

having count(*) > 40

order by count(*) desc


Adding the line: "having count(*) > 40" to our query, we force  it to return only results where the number for contacts for a state is greater than or equal to 40 and get these:

state

count

CA

72

NJ

52

NY

46


Another type of grouping is MIN and MAX. With a "salary" column in our table we can search for the minimum or maximum salaries for each state that we have in our database. In the following query, the contacts with the highest salary for each state would be returned. However, we also have a filter that is only allowing us to see the values for five specific states (added here to show we can filter these too).

select  state,  MAX(salary) [max salary] from contact1

where state IN('NJ','NY','CA','IA','CO')

group by state

 

state

max salary

CA

62500

CO

42000

IA

58000

NJ

61500

NY

75000

 

Here we are able to get the highest and lowest salary values for each state in one query. Please note that because of grouping, if the state of CA had more than one contact with the same highest salary of 62500, the query returns only the one value. Dig deeper, you can get averages too!

select  state,  MAX(salary) [max salary], MIN(salary) [min salary] from contact1

where state IN('NJ','NY','CA','IA','CO')

group by state

 

state

max salary

min salary

CA

62500

62000

CO

42000

40000

IA

58000

58000

NJ

61500

32225

NY

75000

35600

 

Lastly, for this article, we have what is called an AGGREGATE function. They are MIN, MAX, SUM, AVG, and COUNT(*). These are used return a value from a column containing "numeric" data. These are required for the GROUP BY clause we used above but can be used without it lie these queries show:


This query will return us the average salary for all of the contacts:

select AVG(salary) [Avg Salary] from contact1


This query will return us the maximum salary from all of the contacts:

select MAX(salary) [Maximum Salary] from contact1


This query will return us the number of records in the contacts1 table:

select count(*) from contact1

 



That wraps up "Part 2: A Sequel to SQL". Please try these queries and think of ways to use them to get the most out of your data. There is so much more to be learned about using SQL and querying language. The Internet is full of real world examples and great tutorials if you care to dive deeper. Dig in and have fun!

Happy SQL querying!

Find similar articles in these categories:

PRODUCT: wMobile

AUDIENCES: Administrators End Users

Fred Block
Application Specialist at UpCurve Cloud
More From This Author »