SQL分组报表一例(摘自book online)

转载 2006年05月23日 22:54:00
USE pubs
GO
SELECT   Category = 
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

Here is the result set:

Category            Shortened Title           Price                      
------------------- ------------------------- -------------------------- 
Business            You Can Combat Computer S 2.99                       
Business            Cooking with Computers: S 11.95                      
Business            The Busy Executive's Data 19.99                      
Business            Straight Talk About Compu 19.99                      

                                              avg
                                              ==========================
                                              13.73                      

Category            Shortened Title           Price                      
------------------- ------------------------- -------------------------- 
Modern Cooking      The Gourmet Microwave     2.99                       
Modern Cooking      Silicon Valley Gastronomi 19.99                      

                                              avg
                                              ==========================
                                              11.49                      

Category            Shortened Title           Price                      
------------------- ------------------------- -------------------------- 
Popular Computing   Secrets of Silicon Valley 20.00                      
Popular Computing   But Is It User Friendly?  22.95                      

                                              avg
                                              ==========================
                                              21.48                      

Category            Shortened Title           Price                      
------------------- ------------------------- -------------------------- 
Psychology          Life Without Fear         7.00                       
Psychology          Emotional Security: A New 7.99                       
Psychology          Is Anger the Enemy?       10.95                      
Psychology          Prolonged Data Deprivatio 19.99                      
Psychology          Computer Phobic AND Non-P 21.59                      

                                              avg
                                              ==========================
                                              13.50                      

Category            Shortened Title           Price                      
------------------- ------------------------- -------------------------- 
Traditional Cooking Fifty Years in Buckingham 11.95                      
Traditional Cooking Sushi, Anyone?            14.99                      
Traditional Cooking Onions, Leeks, and Garlic 20.95                      

                                              avg
                                              ==========================
                                              15.96                      

(21 row(s) affected)

相关文章推荐

Sport Book 0.3% Rebate Bonus Promotion in Newcity8 Online Casino Malaysia

Sport Book 0.3% Rebate Bonus Promotion in Newcity8 Online Casino Malaysia FreeMalay promotion, Fr...

book shop online

  • 2015年01月28日 09:35
  • 3.24MB
  • 下载

Dynamics 365Online 开发基于fetchxml报表的准备工作

online中的报表的开发方式不同与on-premises,后者可以是sql也可以是fetchxml,但前者必须是fetchxml无法再使用sql,那为了能在我们的报表数据源中选择到fetch这个类型...

分组统计报表

  • 2014年02月18日 16:35
  • 2KB
  • 下载

SQL优化一例,由于动态性能视图统计信息不准确引起的查询缓慢

某库查询锁相关信息语句如下

ireport分组与子报表的区别

  • 2014年09月23日 15:23
  • 533B
  • 下载

【Mysql】复杂SQL调优一例

*相关表已经脱敏处理1.原SQLSELECT tmp.PARTY_ID, s.TICKER_SYMBOL, tmp.SHAREHOLDER_NAME, tmp...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL分组报表一例(摘自book online)
举报原因:
原因补充:

(最多只允许输入30个字)