GROUP BY - Revision history

Bacchas: /* ROLLUP */

2017-11-19T18:07:05Z

ROLLUP

← Older revision		Revision as of 18:07, 19 November 2017
Line 78:		Line 78:

	==ROLLUP==		==ROLLUP==
			<H3> ROOLUP CREATES SUBTOTALS THAT ROLL UP FROM THE MOST DETAILED LEVEL TO A GRAND TOTAL, USING THE GROUPING LIST SPECIFIED IN THE GROUP BY CLAUSE</H3>
	'''Performs increasing levels of cumulative subtotals, based on the provided column list. '''		'''Performs increasing levels of cumulative subtotals, based on the provided column list. '''
	SELECT name, category,		SELECT name, category,

2017-11-19T18:02:39Z

GROUPING SETS

← Older revision		Revision as of 18:02, 19 November 2017
Line 48:		Line 48:

	== GROUPING SETS ==		== GROUPING SETS ==

	'''Enables performing multiple GROUP BY clauses with a single query.'''		'''Enables performing multiple GROUP BY clauses with a single query.'''
	SELECT name, category,		SELECT name, category,

2017-11-19T17:59:29Z

Group by And HAVING

← Older revision		Revision as of 17:59, 19 November 2017
Line 28:		Line 28:

	== Group by And HAVING ==		== Group by And HAVING ==
			<H2> THE WHERE CLAUSE IS USED TO RESTRICT ROWS; THE HAVING CLAUSE IS USED TO RESTRICT GROUPS RETURNED BY A GROUP BY CLAUSE</H2>
	''' TOTAL SALES VOLUME FOR JANUARY BY AND AND PRODUCT AND TOTAL VOLUME GREATER THAN 1000'''		''' TOTAL SALES VOLUME FOR JANUARY BY AND AND PRODUCT AND TOTAL VOLUME GREATER THAN 1000'''

2017-11-19T17:57:57Z

← Older revision		Revision as of 17:57, 19 November 2017
Line 20:		Line 20:

	'''Ex: total sales volume for January group by day and product'''		'''Ex: total sales volume for January group by day and product'''

			'''GROUP ALL EMPLOYEES BY DEPARTMENT, THEN, WITHIN EACH DEPARTMENT, GROUP THEM BY JOB'''
			SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
			FROM EMPLOYEES
			WHERE DEPARTMENT_ID > 40
			GROUP BY DEPARTMENT_ID, JOB_ID;

	== Group by And HAVING ==		== Group by And HAVING ==

2017-11-19T17:48:47Z

← Older revision		Revision as of 17:48, 19 November 2017
Line 1:		Line 1:
			'''Group functions require that any column listed in the SELECT CLAUSE that is not part of a group function must be listed in GROUP BY CLAUSE'''
			*BELOW QUERY GENERATES AN ERROR - NOT A GROUP BY EXPRESSION -
			SELECT JOB_ID, LASTNAME, AVG(SALARY)
			FROM EMPLOYEES
			GROUP BY JOB_ID


	== Returns queries in groups ==		== Returns queries in groups ==
	SELECT SALES_DATE, SUM(TOTAL_AMOUNT)		SELECT SALES_DATE, SUM(TOTAL_AMOUNT)

2017-11-08T21:31:02Z

GROUPING SETS

← Older revision		Revision as of 21:31, 8 November 2017
Line 43:		Line 43:
	(name, category,		(name, category,
	(name,category),());		(name,category),());

			SELECT name, catcode,
			COUNT (isbn), TO_CHAR(AVG(retail), '999.99') "Avg Retail"
			FROM publisher JOIN books USING(pubid)
			WHERE pubid IN (2,3,5)
			GROUP BY GROUPING SETS(name, catcode,(name, catcode),())

	==CUBE==		==CUBE==

2017-11-08T17:23:14Z

Group by And HAVING

← Older revision		Revision as of 17:23, 8 November 2017
Line 27:		Line 27:
	* Include only the categories Computer, Children, and Business.		* Include only the categories Computer, Children, and Business.
	'''The first filtering task should be done with a HAVING clause because it places a condition on an aggregated value. The second filtering task should be handled with a WHERE clause at the row level to include only the book rows in the specified categories before the aggregation is performed.'''		'''The first filtering task should be done with a HAVING clause because it places a condition on an aggregated value. The second filtering task should be handled with a WHERE clause at the row level to include only the book rows in the specified categories before the aggregation is performed.'''
			SELECT catcode, TO_CHAR(AVG(retail-cost), '999,99') "Profit"
			FROM books
			WHERE catcode IN('COM', 'CHN', 'BUS')
			GROUP BY catcode
			HAVING AVG(retail-cost) > 15

	== GROUPING SETS ==		== GROUPING SETS ==

2017-11-08T17:17:50Z

Group by And HAVING

← Older revision		Revision as of 17:17, 8 November 2017
Line 22:		Line 22:
	GROUP BY SALES_DATE, PRODUCT_ID		GROUP BY SALES_DATE, PRODUCT_ID
	HAVING SUM(TOTAL_AMOUNT) > 100;		HAVING SUM(TOTAL_AMOUNT) > 100;

			'''generate a list of each book category along with the average profit, as you did earlier. This query needs to include the following data filters:'''
			* Show only book categories with an average profit greater than $15.00.
			* Include only the categories Computer, Children, and Business.
			'''The first filtering task should be done with a HAVING clause because it places a condition on an aggregated value. The second filtering task should be handled with a WHERE clause at the row level to include only the book rows in the specified categories before the aggregation is performed.'''

	== GROUPING SETS ==		== GROUPING SETS ==

2017-11-07T14:03:20Z

@@ Line 22: / Line 22: @@
   GROUP BY SALES_DATE, PRODUCT_ID
   HAVING SUM(TOTAL_AMOUNT) > 100;
 [[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

2017-11-01T13:55:11Z

← Older revision		Revision as of 13:55, 1 November 2017
Line 22:		Line 22:
	GROUP BY SALES_DATE, PRODUCT_ID		GROUP BY SALES_DATE, PRODUCT_ID
	HAVING SUM(TOTAL_AMOUNT) > 100;		HAVING SUM(TOTAL_AMOUNT) > 100;

			[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]