SQL Command for the following table.

Question

I have 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.

Answer 1

SELECT SUM(Total_Sales) FROM sales GROUP BY (X)

Replace X with Zone, District, State or Distributor.

Answer 2

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