分類彙整: Contests

Controlling the Running Android Emulator

Adapt from
[1] http://developer.android.com/guide/developing/devices/emulator.html


Those keys that cannot be found on the emulated keyboard are in bold letters.

Emulated Device Key / Hardware Key / Effect Keyboard Key
Home HOME
Menu (left softkey) F2 or Page-up
Star (right softkey) Shift-F2 or Page-down
Back ESC
Call/dial F3
Hangup/end call F4
Search F5
Power button F7
Audio volulme up KEYPAD_PLUS, Ctrl-5
Audio volume down KEYPAD_MINUS, Ctrl-F6
Camera/Take a shot Ctrl-KEYPAD_5, Ctrl-F3
Switch to previous layout orientation (e.g. protrait or landscape mode) KEYPAD_7, Ctrl-F11
Switch to next layout orientation (e.g. protrait or landscape mode) KEYPAD_9, Ctrl-F12
Toggle cell networking on/off

F8

Toggle code profiling F9 (only with

-trace

startup option)

Toggle fullscreen mode Alt-Enter
Toggle trackball mode F6
Enter trackball mode temporarily (i.e. pressing key) Delete
DPad left/up/right/down KEYPAD_4/8/6/2
DPad center click KEYPAD_5
Onion alpha increase/decrease KEYPAD_MULTIPLY(*) / KEYPAD_DIVIDE(/)

Debugging in Android - 001

Observation

Cannot see new views(e.g. RadioGroup) after the textbox(i.e. edittext) while running my app with the following XML layout.

Code

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:padding="5dp">
   
    <EditText
        android:id="@+id/edittext"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>
   
   
    <RadioGroup android:id="@+id/people_radioGroup"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content">
      <RadioButton android:id="@+id/radio_red"
          android:layout_width="wrap_content"
          android:layout_height="wrap_content"
          android:text="Red" />
      <RadioButton android:id="@+id/radio_blue"
          android:layout_width="wrap_content"
          android:layout_height="wrap_content"
          android:text="Blue" />
    </RadioGroup>  
   
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Country" />
    <AutoCompleteTextView android:id="@+id/autocomplete_country"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"/>

</LinearLayout>

More Details

API Level: 8

Orientation: Vertical

Solution

The line

android:orientation="horizontal"

is incorrect.

It should be

android:orientation="vertical"

for veritcal orientation.

Android Activity Part 1

Adopt from

[1] http://developer.android.com/guide/topics/fundamentals.html


What is Activity?

From our daily experience, we need to perform some activities before we can finish a job. For instance, a dinner, you need to go to the supermarket, choose the ingredients, bring them home and cook them all. An activity composes of activities and the basic unit is the action.

In Android, the idea of activity restricts on a task completed on the screen. An activity represents a single screen with a user interface. For example, an email application might have one activity that shows a list of new emails, another activity to compose an email, and another activity for reading emails. Although the activities work together to form a cohesive user experience in the email application, each one is independent of the others. As such, a different application can start any one of these activities (if the email application allows it). For example, a camera application can start the activity in the email application that composes new mail, in order for the user to share a picture. [1]

How to implement Activities?

 

In simplest case, your activity needs to inherit from Activity. To create your activity, you need to override to onCreate method. Then, you need to put your view (technically, pass the reference of your view) in to the activity as the definition highlighted above.

public class MyActivity extends Activity {
    public void onCreate(Bundle savedInstanceState) {        
        super.onCreate(savedInstanceState);
        TextView textview = new TextView(this); // Create the view object within the activity class
        textview.setText("This is the a tab");
        setContentView(textview);
    }
}

public class MyActivity extends Activity {
    public void onCreate(Bundle savedInstanceState) {        
        super.onCreate(savedInstanceState);
        TextView textview = (TextView)findViewById(R.id.textview); // Get the reference from project resource (the layout is defined in XML)
        textview.setText("This is the a tab");
        setContentView(textview);
    }
}

IET YMEC 2010

ERG – Energy, Resources, Green

Presentation Skill

Technical Intelligence – Make great Product

Commercial Intelligence – Sale your idea, popularize your idea

Give up J2ME because its library is too small and outdated.

Android - Intent, Activity, Service, Broadcast Receiver, Content Provider

Adopt from :

http://developer.android.com/guide/topics/fundamentals.html

http://developer.android.com/guide/basics/what-is-android.html

Some of the core libraries are listed below:

  • System C library - a BSD-derived implementation of the standard C system library (libc), tuned for embedded Linux-based devices
  • Media Libraries - based on PacketVideo's OpenCORE; the libraries support playback and recording of many popular audio and video formats, as well as static image files, including MPEG4, H.264, MP3, AAC, AMR, JPG, and PNG
  • Surface Manager - manages access to the display subsystem and seamlessly composites 2D and 3D graphic layers from multiple applications
  • LibWebCore - a modern web browser engine which powers both the Android browser and an embeddable web view
  • SGL - the underlying 2D graphics engine
  • 3D libraries - an implementation based on OpenGL ES 1.0 APIs; the libraries use either hardware 3D acceleration (where available) or the included, highly optimized 3D software rasterizer
  • FreeType - bitmap and vector font rendering
  • SQLite - a powerful and lightweight relational database engine available to all applications

A central feature of Android is that one application can make use of elements of other applications (provided those applications permit it).

An activity presents a visual user interface for one focused endeavor the user can undertake.

A service doesn't have a visual user interface, but rather runs in the background for an indefinite period of time.

A broadcast receiver is a component that does nothing but receive and react to broadcast announcements.

A content provider makes a specific set of the application's data available to other applications.

Content providers are activated when they're targeted by a request from a ContentResolver. The other three components — activities, services, and broadcast receivers — are activated by asynchronous messages called intents. An intent is an

<a href="http://developer.android.com/reference/android/content/Intent.html">Intent</a>

object that holds the content of the message. For activities and services, it names the action being requested and specifies the URI of the data to act on, among other things. For example, it might convey a request for an activity to present an image to the user or let the user edit some text. For broadcast receivers, the Intent object names the action being announced. For example, it might announce to interested parties that the camera button has been pressed.

A content provider is active only while it's responding to a request from a ContentResolver. And a broadcast receiver is active only while it's responding to a broadcast message. So there's no need to explicitly shut down these components.

Activities, on the other hand, provide the user interface. They're in a long-running conversation with the user and may remain active, even when idle, as long as the conversation continues. Similarly, services may also remain running for a long time. So Android has methods to shut down activities and services in an orderly way:

  • An activity can be shut down by calling its
    <code><a href="http://developer.android.com/reference/android/app/Activity.html#finish()">finish()</a>

    method. One activity can shut down another activity (one it started with

    startActivityForResult()

    ) by calling

    <code><a href="http://developer.android.com/reference/android/app/Activity.html#finishActivity(int)">finishActivity()</a>

    .

  • A service can be stopped by calling its
    <code><a href="http://developer.android.com/reference/android/app/Service.html#stopSelf()">stopSelf()</a>

    method, or by calling

    <code><a href="http://developer.android.com/reference/android/content/Context.html#stopService(android.content.Intent)">Context.stopService()</a>

    .

The manifest is a structured XML file and is always named AndroidManifest.xml for all applications. It does a number of things in addition to declaring the application's components, such as naming any libraries the application needs to be linked against (besides the default Android library) and identifying any permissions the application expects to be granted.

SQL and XQuery Tutorial for IBM DB2 - XQuery

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao7/section2.html

When to use XML

Situations in which an XML representation is beneficial include:

  • When schema is volatile.
    If the schema of the data changes often, then representing it in relational form may be onerous given the cost and difficulty of changing the associated relational schema. The self-describing nature of XML makes schema modification much simpler.
  • When data is inherently hierarchical.
    Some data is inherently tabular, and a relational model makes the most sense for it. Other data is naturally hierarchical, and XML is often the best representation for it.
  • When data represents business objects in which the component parts do not make sense when removed from the context of that business object.
    For example, consider a standard employee and phone number relationship in which one employee can have multiple phone numbers: one for the office, one for fax, one for home, and one for mobile. If the most frequent usage pattern is to retrieve phone numbers in the context of the employee, it does not make sense to normalize the data and introduce a table solely to track the multiplicity of phone numbers associated with an employee. A better choice may be to keep those phone numbers in the employee relation and represent them using XML.
  • When applications have sparse attributes.
    Some applications have a large number of possible attributes, most of which are sparse or absent for any given data value. A classic example is a merchant catalog; the number of different attributes to track for a given catalog item is huge, including size, color, weave, power requirements and a nearly endless list of other considerations. For any given object, only a subset of these attributes is relevant; the weave of a sweater makes sense but the weave of a lawn mower is nonsensical. Using a relational table to describe the characteristics of the object can be costly and overly complex. Representing such descriptive attribute information as XML data allows for more natural representation as well as less complex and expensive searching.
  • When low-volume data is highly structured.
    In many applications, structured information is critical to the application but it exists in very small quantities. While that information can be represented in a normal relation, this approach can lead to massive relational schemas. Using an XML column with multiple views can dramatically reduce the number of managed objects in a database, and thus reduce the cost of ownership.

Using XQuery
To execute an XQuery directly in DB2 9, you must preface the query with the keyword

xquery

.
Case-sensitive

FLWOR Expression

1.

for
Iterates through an input sequence, binding a variable to each input item in turn
2.

let
Declares a variable and assigns it a value, which may be a list containing multiple items
3.

where
Specifies criteria for filtering query results
4.

order by
Specifies the sort order of the result
5.

return
Defines the result to be returned

Path Expression

Separated by slash(/) characters
Each step in a path expression may also contain a predicate that filters the elements that are returned by that step, retaining only elements that satisfy some condition.

$comments/comment/feedback[type = "suggestion"]/content

Same Result Query

FLWOR Expression

for $y in db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')
return $y

Path Expression

db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')
db2-fn:xmlcolumn

with a parameter that identifies the table name and column name of an XML column in a DB2 table. It returns the sequence of XML documents that is stored in the given column.

The two queries, although producing the same result, are executed slightly differently.

For the FLWOR query, the second line instructs DB2 to iterate through the content sub-elements of the <Comments> elements contained in the aroma.sales.comments column. Each content element is bound in turn to the variable $y. The third line indicates that, for each iteration, the value of $y is returned. The result is a sequence of XML elements.

For the path query, the first step calls the

db2-fn:xmlcolumn

function to obtain a list of XML documents from the Comments column of the aroma.sales table. The second step returns all the <Comments> elements in these documents, the third step returns the <comment> elements nested inside these <Comments> elements, the fourth step returns the <feedback> elements nested inside the <comment> elements, and the fifth step returns the <content> elements nested inside the <feedback> elements.

Text()

db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/feedback/content/text()

Filtering - where

xquery
for $y in db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/feedback
where $y/type = "suggestion" or $y/type = "question"
return $y/content/text()
db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/feedback[type = "suggestion" or type = "question"]/content/text()

HTML Output using FLWOR

xquery
<ul> {
for $y in db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/store_response
where $y/completed = "no"
return <li>{$y/action/text()}</li>
} </ul>

XML Output using FLWOR

xquery
for $y in db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment
where $y/store_response/completed = "no"
return
	(
    if ($y/feedback/type = 'suggestion')
        then
            <suggestion>
            {$y/comment_ID,
             $y/customer_info,
             $y/store_response/action}
            </suggestion>
        else
            <question>
            {$y/comment_ID,
             $y/customer_info,
             $y/store_response/action}
            </question>
)

Order By

xquery
for $y in db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment
where $y/feedback/type = "opinion"
order by $y/store_rating/score
return
    <comment>
        <rating>{$y/store_rating/score/text()}</rating>
        <opinion>{$y/feedback/content/text()}</opinion>
    </comment>

{ } is similar to <?php ?>

distinct-values

xquery
for $t in distinct-values
(db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/feedback/type)
let $tc := db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment[feedback/type = $t]
return
    <comments>
        <type>{$t}</type>
        <count>{count($tc)}</count>
    </comments>

:= is the same as it is in Pascal

Average - avg

xquery
avg(db2-fn:xmlcolumn('AROMA.SALES.COMMENTS')/Comments/comment/store_rating/score)

XQueries with embedded SQL - db2-fn:sqlquery()

for $y in
db2-fn:sqlquery(
    'SELECT Comments
     FROM aroma.sales a, aroma.store b
     WHERE city = ''New York''
         AND a.storekey = b.storekey')/Comments/comment/feedback/content/text()
return $y
db2-fn:sqlquery(

SQL and XQuery tutorial for IBM DB2 - Subqueries

Adopt from
http://www.ibm.com/developerworks/edu/dm-dw-dm-0608cao5-i.html

CASE Syntax - Change the column data properties

SELECT prod_name,
    SUM(CASE WHEN store_name = 'Beaches Brew'
    then dollars else 0 end) AS Beaches,
    SUM(CASE WHEN store_name = 'Cupertino Coffee Supply'
    then dollars else 0 end) AS Cupertino,
    SUM(CASE WHEN store_name = 'Roasters, Los Gatos'
    then dollars else 0 end) AS RoastLG,
    SUM(CASE WHEN store_name = 'San Jose Roasting Company'
    then dollars else 0 end) AS SJRoastCo,
    SUM(CASE WHEN store_name = 'Java Judy''s'
    then dollars else 0 end) AS JavaJudy,
    SUM(CASE WHEN store_name = 'Instant Coffee'
    then dollars else 0 end) AS Instant
FROM aroma.market a,
     aroma.store b,
     aroma.period c,
     aroma.product d,
     aroma.class e,
     aroma.sales f
WHERE   a.mktkey = b.mktkey
    AND b.storekey = f.storekey
    AND c.perkey = f.perkey
    AND d.classkey = e.classkey
    AND d.classkey = f.classkey
    AND d.prodkey = f.prodkey
    AND region LIKE 'West%'
    AND year = 2004
    AND class_type = 'Pkg_coffee'
GROUP BY prod_name
ORDER BY prod_name;

Subqueries

SELECT prod_name, store_name, date, dollars AS sales_05,
	    (SELECT DEC(AVG(dollars),7,2)
	    FROM aroma.product a,
	         aroma.sales b,
	         aroma.period c,
	         aroma.store d
	    WHERE   a.prodkey = b.prodkey
	        AND a.classkey = b.classkey
	        AND c.perkey = b.perkey
	        AND d.storekey = b.storekey
	        AND year = 2004
	        AND month = 'DEC'
	        AND store_name = 'San Jose Roasting Company'
	        AND prod_name LIKE 'Lotta%') AS avg_04
	FROM aroma.product a,
	     aroma.sales b,
	     aroma.period c,
	     aroma.store d
	WHERE   a.prodkey = b.prodkey
	    AND a.classkey = b.classkey
	    AND c.perkey = b.perkey
	    AND d.storekey = b.storekey
	    AND prod_name LIKE 'Lotta%'
	    AND store_name = 'San Jose Roasting Company'
	    AND year = 2005
	    AND month = 'DEC'
	    AND dollars <
	        (SELECT AVG(dollars)
	        FROM aroma.product a,
	             aroma.sales b,
	             aroma.period c,
	             aroma.store d
	        WHERE   a.prodkey = b.prodkey
	            AND a.classkey = b.classkey
	            AND c.perkey = b.perkey
	            AND d.storekey = b.storekey
	            AND year = 2004
	            AND month = 'DEC'
	            AND store_name = 'San Jose  Roasting Company'
	            AND prod_name LIKE 'Lotta%');

Using Subquery in Group By

SELECT sales1.product, jan_04_sales, total_04_sales,
	    DEC((100.00 * jan_04_sales/total_04_sales),7,2) AS pct_of_04
	FROM
	    (SELECT a1.prod_name, SUM(dollars)
	    FROM aroma.product a1,
	         aroma.sales b1,
	         aroma.period c1,
	         aroma.store d1
	    WHERE   a1.prodkey = b1.prodkey
	        AND a1.classkey = b1.classkey
	        AND c1.perkey = b1.perkey
	        AND d1.storekey = b1.storekey
	        AND c1.year = 2004
	        AND c1.month = 'JAN'
	        AND d1.city LIKE 'San J%'
	    GROUP BY a1.prod_name) AS sales1(product, jan_04_sales)
	,
	    (SELECT a2.prod_name, SUM(dollars)
	    FROM aroma.product a2,
	         aroma.sales b2,
	         aroma.period c2,
	         aroma.store d2
	    WHERE   a2.prodkey = b2.prodkey
	        AND a2.classkey = b2.classkey
	        AND c2.perkey = b2.perkey
	        AND d2.storekey = b2.storekey
	        AND c2.year = 2004
	        AND d2.city LIKE 'San J%'
	    GROUP BY a2.prod_name) AS sales2(product, total_04_sales)
	WHERE sales1.product = sales2.product
	ORDER BY pct_of_04 DESC
	FETCH FIRST 10 ROWS ONLY;

SQL0412N Multiple columns are returned from a subquery that is allowed only one column.

Comparison predicates in subqueries

SELECT store_name
		FROM aroma.store
		WHERE city = ANY
		    (SELECT hq_city
		    FROM aroma.market)
		ORDER BY state, city, store_name;

PREDICATE
-EVALUATES TO "TRUE" WHEN
-WHEN NO VALUE IS RETURNED

ALL
-The comparison is true for all values returned by the subquery.
-Evaluates to true.
ANY, SOME
-The comparison is true for at least one of the values returned by the subquery.
-Evaluates to false.
EXISTS
-The subquery produces at least one row.
-Evaluates to false.

SQL and XQuery tutorial for IBM DB2 - Data analysis

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao4/index.html
http://it.toolbox.com/blogs/db2luw/olap-sql-part-5-windowing-aggregates-8373

On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result.

SELECT date, SUM(dollars) AS total_dollars,
    SUM(SUM(dollars)) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS run_dollars,
    SUM(quantity) AS total_qty,
    SUM(SUM(quantity)) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS run_qty
	FROM aroma.period a, aroma.sales b, aroma.product c
	WHERE   a.perkey = b.perkey
	    AND c.prodkey = b.prodkey
	    AND c.classkey = b.classkey
	    AND year = 2006
	    AND month = 'JAN'
	    AND prod_name = 'Aroma Roma'
	GROUP BY date
	ORDER BY date;

OLAP ROW_NUMBER function
You can even use OLAP functions for simple tasks such as providing line numbers in a result set, as in:

SELECT ROW_NUMBER() OVER() AS row_num, order_no, price
FROM aroma.orders;

Using PARTITION BY - Calculate on Each Partition Separately

SELECT date, SUM(dollars) AS total_dollars,
	    SUM(SUM(dollars)) OVER(PARTITION BY week ORDER BY date
	    ROWS UNBOUNDED PRECEDING) AS run_dollars,
	    SUM(quantity) AS total_qty,
	    SUM(SUM(quantity)) OVER(PARTITION BY week ORDER BY date
	    ROWS UNBOUNDED PRECEDING) AS run_qty
	FROM aroma.period a, aroma.sales b, aroma.product c
	WHERE   a.perkey = b.perkey
	    AND c.prodkey = b.prodkey
	    AND c.classkey = b.classkey
	    AND year = 2006
	    AND month = 'JAN'
	    AND prod_name = 'Aroma Roma'
	GROUP BY week, date
	ORDER BY week, date;

The DEC function is used to remove all but two of the decimal places from each Price value

Use Rank()

SELECT store_name, district, SUM(dollars) AS total_sales,
	    RANK() OVER(ORDER BY SUM(dollars) DESC) AS sales_rank
	FROM aroma.market a,
	     aroma.store b,
	     aroma.sales c,
	     aroma.period d
	WHERE   a.mktkey = b.mktkey
	    AND b.storekey = c.storekey
	    AND d.perkey = c.perkey
	    AND year = 2005
	    AND month = 'MAR'
	    AND region = 'West'
	GROUP BY store_name, district;

Using Date Arithmetic

SELECT date - 3 MONTHS - 4 DAYS AS due_date,
	    date AS cur_date,
	    date + 3 MONTHS + 4 DAYS AS past_due
	FROM aroma.period
	WHERE year = 2004
	    AND month = 'JAN';

You can similarly adjust aspects of any TIME datatype (HOURS, MINUTES, and/or SECONDS) or any TIMESTAMP datatype (YEARS, MONTHS, DAYS, HOURS, MINUTES, and/or SECONDS). For more information about DATE, TIME, and TIMESTAMP arithmetic.

Conditions on groups: HAVING clause

SELECT column name(s)
	FROM table name(s)
	[WHERE search_condition]
	[GROUP BY group_list]
	[HAVING condition]
	[ORDER BY order_list];

WHERE Clause
Works on rows of data prior to grouping.
Conditions cannot be expressed with set functions (for example, SUM or AVG), but column aliases for nonaggregate expressions can be used.

HAVING Clause
Works on the result set after grouping. 

 

 

 

 

Conditions can be expressed with any set function or column alias.

Any set function can be referenced in a condition in the HAVING clause. A query with a HAVING clause must contain a GROUP BY clause, unless the select list contains only set functions. For example:

SELECT MIN(prodkey), MAX(classkey)
	FROM aroma.product
	HAVING MIN(prodkey) = 0;

SQL and XQuery tutorial for IBM DB2 - UNION, INTERSECT and EXCEPT

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section12.html
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section13.html
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section15.html
http://msdn.microsoft.com/zh-tw/library/ms191255.aspx

UNION

query_expression UNION | INTERSECT | EXCEPT [ALL] query_expression
[ORDER BY order_list];
使用 EXCEPT,下列查詢會傳回 EXCEPT 運算元左邊查詢結果有、但右邊查詢沒有的任何相異值。

query_expression - Any join or nonjoin query expression, as defined in the SQL Reference Guide .

If the ORDER BY clause is used, the values must reference columns from the select list of the first query expression. Using column names from the second query expression results in an error.

ORDER BY 1, 2

Example UNION query

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars) AS sales
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Medium'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state

		UNION

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars)
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Large'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state
		ORDER BY 2, 1;

Example query (UNION ALL)

		SELECT city
		FROM aroma.store
		UNION ALL
		SELECT hq_city AS city
		FROM aroma.market
		ORDER BY city;

Using UNION ALL if you want to repeat values that occur more than once in the list of unioned objects. For example, you may want to count the total number of objects in each case.

SQL and XQuery tutorial for IBM DB2 - Group By Clause

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section10.html

Set functions operate on all rows of a result table or on groups of rows defined by a GROUP BY clause. For example, you can group the sales for each market and calculate the respective sum, maximum, and minimum values.

Syntax of the GROUP BY clause

		SELECT column name(s)
		FROM table name(s)
		[WHERE search_condition]
		[GROUP BY group_list]
		[ORDER BY order_list];

Using Multiple groups

SELECT year, region, district, city, SUM(dollars) AS sales
FROM aroma.store a, aroma.sales b, aroma.product c,
     aroma.period d, aroma.market e
WHERE   a.storekey = b.storekey
    AND c.prodkey = b.prodkey
    AND c.classkey = b.classkey
    AND d.perkey = b.perkey
    AND e.mktkey = a.mktkey
    AND year IN (2004, 2005)
GROUP BY year, region, district, city
ORDER BY year, region, district, city;