Summarizing Data Using the CASE Expression : Case « Query « SQL Server / T-SQL Tutorial






Selecting the total sales by weekday--simple query.

8>
9> CREATE TABLE sales(
10>    stor_id        char(4)           NOT NULL,
11>    ord_num        varchar(20)       NOT NULL,
12>    ord_date       datetime          NOT NULL,
13>    qty            smallint          NOT NULL,
14>    payterms       varchar(12)       NOT NULL,
15>    title_id       varchar(80)
16> )
17> GO
1> insert sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1')
2> insert sales values('2', 'D4482',    '09/14/94', 10, 'Net 60',    '1')
3> insert sales values('3', 'N914008',  '09/14/94', 20, 'Net 30',    '2')
4> insert sales values('4', 'N914014',  '09/14/94', 25, 'Net 30',    '3')
5> insert sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3')
6> insert sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')
7> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> SELECT  sum(qty) AS Total,
4>         datename(weekday, ord_date) AS Weekday
5> FROM    sales
6> GROUP BY datename(weekday, ord_date)
7> GO
Total       Weekday
----------- ------------------------------
         75 Tuesday
         80 Wednesday

(2 rows affected)
1>
2> drop table sales;
3> GO
1>








1.23.Case
1.23.1.The syntax pattern for case
1.23.2.The syntax of the searched CASE function
1.23.3.A Simple CASE
1.23.4.CASE Expression
1.23.5.Automatic Code Generation for CASE Expressions
1.23.6.Summarizing Data Using the CASE Expression
1.23.7.Using a CASE expression to sum sales by weekday.
1.23.8.Using the CASE Expression with Complex Conditions
1.23.9.Summarizing Data Using the Searched CASE Expression
1.23.10.A SELECT statement that uses a simple CASE function
1.23.11.A SELECT statement that uses a searchable CASE function
1.23.12.Searched CASE expression looks for the first expression that evaluates to true.
1.23.13.Update statement based on case condition
1.23.14.Case when else
1.23.15.Alias for case statement
1.23.16.Use two case statements in one select statement
1.23.17.Case then with query
1.23.18.Case with range
1.23.19.Doing a calculation in a Searched CASE