This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:activecounts:sql [2011/01/21 16:32] els |
admin:indexing:activecounts:sql [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 21: | Line 22: | ||
| First, the user requested to see the states and their counts: | First, the user requested to see the states and their counts: | ||
| + | <code> | ||
| select STATE, count(*) | select STATE, count(*) | ||
| from LIST | from LIST | ||
| group by STATE; | group by STATE; | ||
| + | </code> | ||
| The user selected the states in the Western United States, and then requested to see the Genders and their counts: | The user selected the states in the Western United States, and then requested to see the Genders and their counts: | ||
| + | <code> | ||
| select GENDER, count(*) | select GENDER, count(*) | ||
| from LIST | from LIST | ||
| where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | ||
| group by GENDER; | group by GENDER; | ||
| + | </code> | ||
| The user selected the Women in the Western United States, and then requested to see the Educations and their counts: | The user selected the Women in the Western United States, and then requested to see the Educations and their counts: | ||
| + | <code> | ||
| select EDUCATION, count(*) | select EDUCATION, count(*) | ||
| from LIST | from LIST | ||
| Line 39: | Line 45: | ||
| GENDER = 'F' | GENDER = 'F' | ||
| group by EDUCATION; | group by EDUCATION; | ||
| + | </code> | ||
| The user selected those people with graduate degrees, and may have continued narrowing his search. | The user selected those people with graduate degrees, and may have continued narrowing his search. | ||
| Line 44: | Line 51: | ||
| As you can see, these SQL statements simple request counts that are filtered by the criteria entered so far, grouped by the next value they want to see. The simplicity of this approach allows it to be more easily integrated into an application. | As you can see, these SQL statements simple request counts that are filtered by the criteria entered so far, grouped by the next value they want to see. The simplicity of this approach allows it to be more easily integrated into an application. | ||
| - | === More Complex SQL === | + | ==== More Complex SQL ==== |
| - | The actual SQL statements for the example on the previous page was slightly more complex, only in that it referenced dimension tables to provide descriptions rather than codes. The actual SQL statements are as follows: | + | The actual SQL statements for the example on the previous page was slightly more complex, only in that it references dimension tables to provide descriptions rather than codes. The actual SQL statements are as follows: |
| First, the presentation of the States: | First, the presentation of the States: | ||
| + | <code> | ||
| select STATES.DESCRIPTION, | select STATES.DESCRIPTION, | ||
| LIST.STATE, | LIST.STATE, | ||
| Line 57: | Line 65: | ||
| group by STATES.DESCRIPTION, | group by STATES.DESCRIPTION, | ||
| LIST.STATE; | LIST.STATE; | ||
| + | </code> | ||
| Notice that the DESCRIPTION column was retrieved for presentation, and the STATE column was retrieved for inclusion in future criteria. | Notice that the DESCRIPTION column was retrieved for presentation, and the STATE column was retrieved for inclusion in future criteria. | ||
| Line 62: | Line 71: | ||
| Second, the presentation of the Genders: | Second, the presentation of the Genders: | ||
| + | <code> | ||
| select GENDERS.DESCRIPTION, | select GENDERS.DESCRIPTION, | ||
| LIST.GENDER, | LIST.GENDER, | ||
| Line 70: | Line 80: | ||
| group by GENDERS.DESCRIPTION, | group by GENDERS.DESCRIPTION, | ||
| LIST.GENDER; | LIST.GENDER; | ||
| + | </code> | ||
| Third, the presentation of the Educations: | Third, the presentation of the Educations: | ||
| + | <code> | ||
| select $ifnull(EDUCATION.DESCRIPTION, case LIST.EDUCATION | select $ifnull(EDUCATION.DESCRIPTION, case LIST.EDUCATION | ||
| when ' ' then 'Did not specify' | when ' ' then 'Did not specify' | ||
| Line 86: | Line 98: | ||
| LIST.EDUCATION | LIST.EDUCATION | ||
| order by LIST.EDUCATION; | order by LIST.EDUCATION; | ||
| + | </code> | ||
| Notice that this SQL statement is more complex, involving $IFNULL functions and Left Outer Joins. These were beneficial because not every row in the LIST table had a value in the EDUCATION column. This SQL would be the standard approach for obtaining the descriptions for those entries that did have a value. This example shows that the SQL can be more complex and still be used in ActiveCounts. | Notice that this SQL statement is more complex, involving $IFNULL functions and Left Outer Joins. These were beneficial because not every row in the LIST table had a value in the EDUCATION column. This SQL would be the standard approach for obtaining the descriptions for those entries that did have a value. This example shows that the SQL can be more complex and still be used in ActiveCounts. | ||