Data Model > Accessing the UNICOM Intelligence Data Model > Working with the Case Data Model > SQL syntax > SQL queries > Advanced hierarchical SQL queries
 
Advanced hierarchical SQL queries
This topic supplements Hierarchical SQL queries and Advanced SQL queries with some examples of advanced SQL queries on the HDATA hierarchical view of the data. The examples are based on the UNICOM Intelligence Data File version of the Household sample data set. The following diagram shows the levels structure in the Household sample.
Each case at the top level corresponds to a household. For more information about the household sample, including details of the questionnaire logic and the variables at each level, see The Household sample.
You can run the example queries shown below using the DM Query sample tool, which is supplied with the UNICOM Intelligence Developer Documentation Library. Except where stated otherwise, these examples were run using the connection option to return category names rather than category values. You select this option on the Advanced tab in the Data Link Properties dialog box. To set up DM Query to use the Household sample data set, see the second example in Understanding hierarchical data.
1. Gender by Region at the Person level
SELECT GroupBy.Col[0] As Gender,
COUNT(^.Region) As [Base],
SUM(^.Region={North}) As North,
SUM(^.Region={South}) As South,
SUM(^.Region={East}) As East,
SUM(^.Region={West}) As West
FROM HDATA.Person
WHERE Gender IS NOT NULL
GROUP BY Gender ON {Male,Female} WITH(BaseSummaryRow)
Here is the result set:
Gender Base North South East West
-------- ---- ----- ----- ---- ----
{} 25 4 11 7 3
{male} 12 1 6 4 1
{female} 13 3 5 3 2
In this example, the results show the number of cases at the Person level. Each case at the Person level corresponds to a person in a household.
2. Gender by Region at the HDATA level
SELECT GroupBy.Col[0] As Gender,
COUNT(Region) As [Base],
SUM(Region={North}) As North,
SUM(Region={South}) As South,
SUM(Region={East}) As East,
SUM(Region={West}) As West
FROM HDATA
WHERE SUM(Person.(Gender))>{}
GROUP BY SUM(Person.(Gender)) ON {Male,Female} WITH(BaseSummaryRow)
Here is the result set:
Gender Base North South East West
-------- ---- ----- ----- ---- ----
{} 10 2 3 3 2
{male} 8 1 3 3 1
{female} 8 1 3 2 2
In this example, the results show the number of cases at the HDATA level, which in the Household sample correspond to households.
Note Gender has been up-leved using SUM. When used on categorical data, SUM takes the union.
3. Age (auto-banded) by Gender at the Person Level
SELECT GroupBy.Col[0] As Age,
COUNT(Gender) As [Base],
SUM(Gender={Male}) As Male,
SUM(Gender={Female}) As Female
FROM HDATA.Person
WHERE Age IS NOT NULL
GROUP BY Age.Band(0, 10, 10) ON {1,2,3,4,5,6,7,8,9,10}
WITH(BaseSummaryRow)
In the Household sample, Age is a numeric variable. This example uses the Band function to group the numeric age values into 10 age groups. Note that this query is designed to be run using the connection option to return category values. (This query is not suitable for running with the connection option to return category names, because the numeric category values returned by the Band function do not correspond to mapped category values.)
Here is the result set:
Age Base Male Female
---- ---- ---- ------
{} 25 12 13
{1} 6 4 2
{2} 2 1 1
{3} 5 2 3
{4} 6 3 3

{5} 3 1 2
{6} 0 0 0
{7} 1 0 1
{8} 1 0 1
{9} 1 1 0
{10} 0 0 0
In this example, the results show the number of people.
4. Age (auto-banded) by Gender at the HDATA Level
SELECT GroupBy.Col[0] As Age,
SUM(COUNT(Person.(Gender))>0) As [Base],
SUM(SUM(Person.(Gender={Male}))>0) As Male,
SUM(SUM(Person.(Gender={Female}))>0) As Female
FROM HDATA
WHERE SUM(Person.(Age.Band(0, 10,10)))>{}
GROUP BY SUM(Person.(Age.Band(0, 10,10))) ON {1,2,3,4,5,6,7,8,9,10}
WITH(BaseSummaryRow)
Like the previous example, this query is designed to be run using the connection option to return category values.
Here is the result set:
Age Base Male Female
---- ---- ---- ------
{} 10 8 8
{1} 3 3 3
{2} 1 1 1
{3} 3 2 1
{4} 4 4 3
{5} 2 2 2
{6} 0 0 0
{7} 1 0 1
{8} 1 1 1
{9} 1 1 1
{10} 0 0 0
In this example, the results show the number of households.
5. Gender by Purpose at the Trip Level
SELECT GroupBy.Col[0] As Gender,
COUNT(Purpose) As [Base],
SUM(Purpose>={business}) As Business,
SUM(Purpose>={vacation}) As Vacation,
SUM(Purpose>={education}) As Education,
SUM(Purpose>={visiting_relatives}) As [Visiting Relatives],
SUM(Purpose>={sport}) As Sport
FROM HDATA.Person.Trip
WHERE ^.Gender IS NOT NULL
GROUP BY ^.Gender ON {Male, Female}
WITH(BaseSummaryRow)
Here is the result set:
Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------------ -----
{} 24 6 15 1 5 2
{male} 11 0 9 0 3 2
{female} 13 6 6 1 2 0
In this example, the results show the number of cases at the Trip level. Each case at the Trip level corresponds to an overseas trip.
6. Gender by Purpose at the Person Level
SELECT GroupBy.Col[0] As Gender,
SUM(COUNT(Trip.(Purpose))>0) As [Base],
SUM(SUM(Trip.(Purpose>={business}))>0) As Business,
SUM(SUM(Trip.(Purpose>={vacation}))>0) As Vacation,
SUM(SUM(Trip.(Purpose>={education}))>0) As Education,
SUM(SUM(Trip.(Purpose>={visiting_relatives}))>0) As [Visiting Relatives],
SUM(SUM(Trip.(Purpose>={sport}))>0) As Sport
FROM HDATA.Person
WHERE Gender IS NOT NULL
GROUP BY Gender ON {Male, Female}
WITH(BaseSummaryRow)
Here is the result set:
Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------
------ -----
{} 12 1 12 1 4 2
{male} 6 0 6 0 2 2
{female} 6 1 6 1 2 0
In this example, the results show the number of people who took overseas trips.
7. Region by Purpose at the HDATA Level
SELECT GroupBy.Col[0] AS Region,
SUM(SUM(Person.(SUM(trip.(purpose > {}))))>0) AS [Base],
SUM(SUM(Person.(SUM(trip.(purpose >= {business}))))>0) AS Business,
SUM(SUM(Person.(SUM(trip.(purpose >= {vacation}))))>0) AS Vacation,
SUM(SUM(Person.(SUM(trip.(purpose >= {education}))))>0) AS Education,
SUM(SUM(Person.(SUM(trip.(purpose >= {visiting_relatives}))))>0) AS "Visiting Relatives",
SUM(SUM(Person.(SUM(trip.(purpose >= {sport}))))>0) AS Sport
FROM hdata
GROUP BY Region ON {north, south, east, west} WITH (BaseSummaryRow)
Here is the result set:
Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------------ -----
{} 12 1 12 1 4 2
{male} 6 0 6 0 2 2
{female} 6 1 6 1 2 0
The result set shows the number of households in each region that have taken at least one overseas trip. This query demonstrates up-leving data by two levels, that is, the data in the Trip-level Purpose variable has been up-leved twice to the top (HDATA) level.
See also
Hierarchical SQL queries
Advanced SQL queries
Hierarchical expressions
Expression evaluation
UNICOM Intelligence Function Library
Aggregate functions
Basic SQL queries
SQL syntax
SQL queries