英文:
How to sum two calculated rows together
问题
使用Pervasive SQL。我有一个查询，结果如下所示。然而，N00和S00是同一个人。所以，我需要将N00和S00的“实际”值合并在一行中。我一直在尝试使用子查询，但我不确定如何告诉它将这两行相加。
select sum(amount) as Actual,
case when salesman_number = 'N00' or salesman_number = 'S00'
then 'S00,G00'
else salesman_number
end as Salesman_Number
from gcg_7095_bookings
where prod_line IN ('01','03')
and salesman_number in ('G00','M00','N00','S00','O00','T00')
and order_date >= '2023-01-01'
group by case when salesman_number = 'N00' or salesman_number = 'S00'
then 'S00,G00'
else salesman_number
end
结果：
| Actual | Salesman_Number |
|---|---|
| 284315.71 | G00 |
| 223959.78 | M00 |
| 373578.72 | O00 |
| 400546.92 | S00,G00 |
| 76325.80 | T00 |
我需要的结果：
| Actual | Salesman_Number |
|---|---|
| 284315.71 | G00 |
| 223959.78 | M00 |
| 373578.72 | O00 |
| 400546.92 | S00,G00 |
| 76325.80 | T00 |
英文:
Using Pervasive SQL. I have a query w/ results below. However, N00 and S00 are the same person. so, instead of a separate line I need the "actual" values from N00 and S00 to be totaled together in one row. I have been trying to come up with a sub query but I'm not sure how to tell it to add the two rows together.
select sum(amount) as Actual,salesman_number
from gcg_7095_bookings
where prod_line IN ('01','03') and salesman_number in ('G00','M00','N00','S00','O00','T00') and order_date >= '2023-01-01'
group by salesman_number
Results:
| Actual | Salesman_Number |
|---|---|
| 284315.71 | G00 |
| 223959.78 | M00 |
| 45517.92 | N00 |
| 373578.72 | O00 |
| 355029.00 | S00 |
| 76325.80 | T00 |
What I need:
| Actual | Salesman_Number |
|---|---|
| 284315.71 | G00 |
| 223959.78 | M00 |
| 373578.72 | O00 |
| 400546.92 | S00,G00 |
| 76325.80 | T00 |
答案1
得分: 1
我找到了下面的解决方案。Gordon Linoff的答案。在group by中添加一个case语句。
https://stackoverflow.com/questions/27549856/merging-rows-in-sql?rq=2
select sum(amount) as acutal,(case when salesman_number IN ('N00','S00') then 'DG' else salesman_number end) as salesman_number
from gcg_7095_bookings
where prod_line IN ('01','03') and salesman_number in ('G00','M00','N00','S00','O00','T00') and order_date >= '2023-01-01'
group by (case when salesman_number in ('N00','S00') then 'DG' else salesman_number end)
英文:
I found a solution below. Gordon Linoff's answer. adding a case statement in the group by
https://stackoverflow.com/questions/27549856/merging-rows-in-sql?rq=2
select sum(amount) as acutal,(case when salesman_number IN ('N00','S00') then 'DG' else salesman_number end) as salesman_number
from gcg_7095_bookings
where prod_line IN ('01','03') and salesman_number in ('G00','M00','N00','S00','O00','T00') and order_date >= '2023-01-01'
group by (case when salesman_number in ('N00','S00') then 'DG' else salesman_number end)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论