This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
admin:indexing:activecounts:sql [2011/01/21 16:33] 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 50: | Line 57: | ||
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. |