在Postgres中，如何在同一查询中计算和查询，然后对结果进行数学运算？ [英] In Postgres, how do I COUNT and WHERE in the same query, then do math on result?

问题描述

我有一个 SALES_ORDERS 的大型数据库，我需要确定 SELLING_AGENT 在 ORDER 。

I have a large DB of SALES_ORDERS, that I need to determine the % each SELLING_AGENT had in the ORDER.

学习postgresql，这只是一个示例，包含约50万行数据。每个 ORDER＃可以由数十个 SALE_ITEM 组成（但仅由'SALE_ITEM_1组成'和'SALE_ITEM_2'）。

Learning postgresql, and this is just a sample, of ~500k rows of data. Each ORDER# could be composed of dozens of SALE_ITEMs (but only composed of 'SALE_ITEM_1' and 'SALE_ITEM_2').

我想我可以尝试做一个SUM选择这样的东西：

I thought I could try doing a SUM SELECT something like this:

    SELECT (SUM(sale_item_1)+sum(sale_item_2)) as total_person_sale
    FROM SALES_ORDER
    WHERE orderNumber LIKE orderNumber
    GROUP by SELLING_AGENT

但显然我不理解WHERE语句用于计数相似

But clearly I'm not grasping the WHERE statement use to count similar

希望是要填写最后一列，例如：
卖方：

The hope is to fill out the final column, like this: selling agent:

    selling agent:  Order#  Sale_item_1 Sale_item_2 %_of_TOTAL_order
    jim              abc    $1.00       $2.00       14.29%
    steve            abc    $3.00       $4.00       33.33%
    carl             abc    $5.00       $6.00       52.38%
    carl             def    $7.00       $8.00       31.25%
    steve            def    $9.00       $9.00       37.50%
    jim              def    $8.00       $7.00       31.25%
    steve            ghi    $6.00       $5.00       61.11%
    patty            ghi    $4.00       $3.00       38.89%

推荐答案

类似这样的东西：

SELECT 
  s.*, 
  ROUND (
  100.00 * (s.sale_item_1 + s.sale_item_2) /
  (SELECT 
     SUM(sale_item_1 + sale_item_2)
     FROM sales_db 
     WHERE orderNumber = s.orderNumber), 2) percentage
FROM sales_db s;

请参见演示

这篇关于在Postgres中，如何在同一查询中计算和查询，然后对结果进行数学运算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！