如何创建具有SUM或AVG视图取决于条件

问题描述：

我想要得到的相同的结果即使我添加的GROUP列BY部分或没有这种情况：
我有一个表包含sale_id，PRODUCT_ID，命令数量和交货数量
我想的product_id得到，如果分组命令数量的总和数据如何创建具有SUM或AVG视图取决于条件

CREATE TABLE sale_delivery (id serial unique , sale_id INTEGER, product_id INTEGER, sale_qty Float, delivered_qty Float); 
INSERT INTO sale_delivery VALUES(106827,3063,3135,1100.000,44.0); 
INSERT INTO sale_delivery VALUES(106828,3063,3155,400.000,11.0); 
INSERT INTO sale_delivery VALUES(106958,3063,3155,400.000,44.0); 
INSERT INTO sale_delivery VALUES(106959,3063,3135,1100.000,374.0); 
INSERT INTO sale_delivery VALUES(107646,3063,3135,1100.000,143.0); 
INSERT INTO sale_delivery VALUES(108871,3063,3135,1100.000,143.0); 
INSERT INTO sale_delivery VALUES(108872,3063,3155,400.000,44.0); 
INSERT INTO sale_delivery VALUES(109127,3063,3155,400.000,44.0); 
INSERT INTO sale_delivery VALUES(109128,3063,3135,1100.000,143.0); 
INSERT INTO sale_delivery VALUES(109627,3063,3135,1100.000,187.0); 
INSERT INTO sale_delivery VALUES(109628,3063,3155,400.000,44.0); 
INSERT INTO sale_delivery VALUES(109682,3063,3135,1100.000,66.0); 
INSERT INTO sale_delivery VALUES(110103,3063,3155,400.000,213.0);

这里是查询：

SELECT 
    sale_id, 
    COUNT(product_id), 
    SUM(sale_qty)/COUNT(product_id) sale_qty , 
    SUM(delivered_qty) deliveryed_qty 
-- AVG(sale_qty) sale_qty, 
-- SUM(deliveryed_qty) deliveryed_qty, 
FROM sale_delivery 
WHERE sale_id=3063 
GROUP BY sale_id 
     ,product_id

但如果我评论的最后一行我得到的结果作为AVG的COUNT（*）不数（产品）！
这里是查询的链接 http://sqlfiddle.com/#!9/8cd9d9/2
我想要得到的相同的结果没有最后一行,product_id

编辑你的问题，表明你希望结果实现。 –

@GordonLinoff如果你想得到相同的结果，那么我在最后一行 – m3asmi

中加了它，为什么你删除了最后一行。？ –

答

SELECT sale_id, avg(product_idCNT), SUM(sale_qty)/COUNT(product_id) sale_qty , SUM(delivered_qty) deliveryed_qty 
 
    FROM 
 
    (
 
SELECT 
 
\t sale_id, 
 
\t COUNT(product_id) as product_idCNT, 
 
\t sum(sale_qty) as sale_qty, 
 
\t COUNT(product_id) as product_id, 
 
\t SUM(delivered_qty) AS delivered_qty 
 
FROM sale_delivery 
 
WHERE sale_id=3063 
 
GROUP BY sale_id 
 
    
 
\t 
 

 
\t )p 
 

 
\t GROUP BY sale_id

我试过它看起来像count（product_id）= count（*）!! – m3asmi

如何创建具有SUM或AVG视图取决于条件

相关推荐