I ha开发者_JAVA技巧ve a table named with "Sales" having the following columns:
Sales_ID|Product_Code|Zone|District|State|Distributor|Total_Sales
Now i want to generate a sales summary to view the total sales by zone and then by district and then by State by which distributor for the last/past month period.
How can i write a Sql Statement to do this? Can anyone help me Plz. Thanks in advance.
And i have another question that, how can i select the second largest or third largest values from any column of a table.
Have a look at using the ROLLUP GROUP BY option.
Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows,
and also a grand total row.
The number of groupings that is returned equals the number of expressions
in the <composite element list> plus one. For example, consider the following statement.
Copy Code
SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c)
One row with a subtotal is generated for each unique combination of values of
(a, b, c), (a, b), and (a). A grand total row is also calculated.
Columns are rolled up from right to left.
The column order affects the output groupings of ROLLUP and can affect the number
of rows in the result set.
Something like
DECLARE @Table TABLE(
Zone VARCHAR(10),
District VARCHAR(10),
State VARCHAR(10),
Sales FLOAT
)
INSERT INTO @Table SELECT 'A','A','A',1
INSERT INTO @Table SELECT 'A','A','B',1
INSERT INTO @Table SELECT 'A','B','A',1
INSERT INTO @Table SELECT 'B','A','A',1
SELECT Zone,
District,
State,
SUM(Sales)
FROM @Table
WHERE <Your Condition here> --THIS IS WHERE YOU USE THE WHERE CLAUSE
GROUP BY ROLLUP (Zone,District,State)
To Get the second and 3rd largets, you can use either (ROW_NUMBER (Transact-SQL))
;WITH Vals AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY RequiredCol DESC) RowNum
FROM YourTable
)
SELECT *
FROM Vals
WHERE RowNum IN (2,3)
or
SELECT TOP 2
*
FROM (
SELECT TOP 3
*
FROM YourTable
ORDER BY RequiredCol DESC
) sub
ORDER BY RequiredCol
SELECT SUM(Total_Sales) FROM sales GROUP BY (X)
Replace X with Zone
, District
, State
or Distributor
.
精彩评论