如何做一个特定列的总和？ [英] how to do the sum of a particular column ?

问题描述

你好......我想计算金额的总和......但是当我在ms-access sql视图中运行时，我得到一个空白列，当我在软件中使用此查询时，没有任何内容出现。

hello ... i want to calculate the sum of the amount ... but i am getting a blank column when i run in ms-access sql view and nothing appears when i use this query in software.

SELECT AllocateAsset.Item, AllocateAsset.ItemModel, Count(AllocateAsset.ItemModel) AS CountOfItem, (CountOfItem*(SELECT Rate FROM rateList  WHERE rateList.MakeModel=AllocateAsset.ItemModel ))/4 AS Amount, SUM(Amount) AS Total

FROM aboutPM INNER JOIN (AllocateAsset INNER JOIN PMReport ON AllocateAsset.ID=PMReport.AssetID) ON aboutPM.ID=PMReport.AboutPMID

WHERE (((aboutPM.Year)="2015") AND ((aboutPM.Period)="Jan-Feb-Mar"))

GROUP BY AllocateAsset.Item, AllocateAsset.ItemModel

ORDER BY AllocateAsset.Item;

请帮帮我

please help me out

推荐答案

试试这个：

Try this:

SELECT t1.*, SUM(t1.Amount) AS Total
FROM (
    SELECT AllocateAsset.Item, AllocateAsset.ItemModel, Count(AllocateAsset.ItemModel) AS CountOfItem,
        (CountOfItem*(SELECT Rate FROM rateList  WHERE rateList.MakeModel=AllocateAsset.ItemModel ))/4 AS Amount
    FROM aboutPM INNER JOIN 
        (AllocateAsset INNER JOIN 
            PMReport ON AllocateAsset.ID=PMReport.AssetID) ON aboutPM.ID=PMReport.AboutPMID
    WHERE (((aboutPM.Year)="2015") AND ((aboutPM.Period)="Jan-Feb-Mar"))
    GROUP BY AllocateAsset.Item, AllocateAsset.ItemModel
    ORDER BY AllocateAsset.Item;
) AS t1
GROUP BY t1.Item, t1.ItemModel, t1.CountOfItem

年字段是varchar数据类型？它应该是数字（整数）数据类型！

Does Year field is varchar data type? It should be numeric(integer) data type!

这篇关于如何做一个特定列的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！