CODEWITHSUNDEEP
sqldatabase-designtwo-way
johnwalker10
johnwalker10

Reputation: 45

How do you read two-way tables?

I want to know what is two-way tables in SQL?

And how can i read these two-way tables

Upvotes: 0

Views: 645

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

Two-way tables is no way of storing data, but of displaying data. It doesn't say anything about how the data is stored.

Let's say we store persons along with their IQ and the country they live in. The table may look like this:

name         iq    country
John Smith   125   GB
Mary Jones   150   GB
Juan Lopez   150   ES
Liz Allen    125   GB

The two-way table to show the relation between IQ and country would be:

   | 125  | 150
---+------+----
GB |   2  |   1
ES |   0  |   1

or

    | GB  | ES
----+-----+---
125 |  2  |  0
150 |  1  |  1

In order to retrieve this data from the database you might write this query:

select iq, country, count(*)
from persons
group by iq, country;

SQL is meant to retrieve data; it is not really meant to care about it's presentation, the layout. So you'd write a program (in PHP, C#, Java, whatever) sending the query to the database, receiving the data and filling a GUI grid in a loop.

In rare cases SQL can provide the layout itself, i.e. give you the data in columns and rows. This is when the attributes of one dimensions are known beforehand. This is usually not the case with IQs or countries as in the example given (i.e. you wouldn't have known which countries and which IQs are present in the table, if I hadn't shown you). But of course you could have retrieved either the countries or the IQs first and then build the main query dynamically (with conditional aggregation or pivot). Another case when values are known beforehand is booleans, e.g. a flag in the persons table to show whether a person is homeless. If you wanted results for how many homeless persons in which countries, you could easily write a query with two columns for homeless and not homeless.

As mentioned: that you can display data in a two-way table doesn't say anything about how this data is stored. Above I showed you a one table example. But let's say you have stores in many cities and want to know in which cities live thinner or thicker people. You decide to check which t-shirt sizes you sold in which cities. So you take your clients orders, look up the clients and the cities they live in. You also look up the order details and the items they refer to, then take all items of type t-shirt. There are many tables involved, but the result is again a two-sided table showing the relation of two attributes. E.g:

city        | S   | M   | L   | XL 
------------+-----+-----+-----+-----
New York    |  5% |  8% |  7% | 10%
Los Angeles | 10% |  7% |  7% |  8%
Chicago     |  1% |  4% |  6% | 11%
Houston     |  2% |  2% |  5% |  7%

Upvotes: 1

Related Questions