如何获取此sql查询：按金额显示记录

I want to display amount of sales from a table in this format:

我想以这种格式显示表中的销售额:

Sales             qty
------------------------------------------
Sales > 50,000    500
Sales < 2000      200
Sales > 15,000    600
------------------------------------------

How can I create this?

我该如何创建呢?

I can write a query for one amount where > 50000 but in this case there is three 50,000 , 2000, 15,000 and how can I show all this in the result?

我可以写一个查询一个金额> 50000,但在这种情况下有三个50,000,2000,15,000,我怎么能在结果中显示所有这些?

2 个解决方案

#1

One way to do it is to simply use union all:

一种方法是简单地使用union all:

select 'Sales > 50,000' as sales,
       count(*)         as qty
       where sales > 50000
union all
select 'Sales <  2,000' as sales,
       count(*)         as qty
       where sales < 2000
union all
select 'Sales > 15,000' as sales,
       count(*)         as qty
       where sales > 15000 and sales <= 50000

Note that last one, I'm assuming you want it exclusive from those above 50,000 (otherwise a row with sales of 60,000 shows up in both sections).

请注意,最后一个,我假设你想要它超过50,000以上的那些(否则两个部分中显示销售额为60,000的行)。

#2

SELECT SUM(A),
  SUM(B),
  SUM(C)
FROM
  (SELECT
    CASE
      WHEN Sales > 50000
      THEN 1
      ELSE 0
    END AS A,
    CASE
      WHEN Sales < 2000
      THEN 1
      ELSE 0
    END AS B,
    CASE
      WHEN Sales > 15000
      THEN 1
      ELSE 0
    END AS C
  FROM table
  )

#1