How To ?

Q : What is sql windows function  ? Ans  : a windows sql function will perform aggregation for each row in result set  , letting  each row in  the result set , leatting each row retain its identity . Example  : mysql> CREATE TABLE t(i INT); mysql> INSERT INTO t VALUES (1),(2),(3),(4); mysql> SELECT SUM(i) AS sum FROM t; +------+ | sum  | +------+ |   10 | +------+ mysql> SELECT i, SUM(i) OVER () AS sum FROM t; +------+------+ | i    | sum  | +------+------+ |    1 |   10 | |    2 |   10 | |    3 |   10 | |    4 |   10 | So  you can see only by  using over()  windows function  , sum is showing along with  each value that  has been summed . Q: what are types of windows sql function  ? Ans :  sql aggregate function can be used in two ways  :             A: -  Windows Function .             B:  -    Specialized window function .        A: windows function  :  count ,sum avg, min ,max , BIT_OR ,BIT_AND ,BIT_XOR           ,ST

MySql Rollup function : The  GROUP BY  clause permits a  WITH ROLLUP  modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations.  ROLLUP  thus enables you to answer questions at multiple levels of analysis with a single query. For example,  ROLLUP  can be used to provide support for OLAP (Online Analytical Processing) operations. Suppose that a  sales  table has  year ,  country ,  product , and  profit  columns for recording sales profitability: CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT ); To summarize table contents per year, use a simple  GROUP BY  like this: mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year ASC; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | +------+--------+ Now Same Example with rollup function mysql> SELECT year, SUM(profit) AS profit