如何在sql中产生每列的总和？ [英] How to produce sum of each column in sql?

问题描述

我正在尝试在Gridview中添加明智的列，但不能。

像我填写gridview从存储过程，它给出这些值。

  DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal 
 03/10/2015 792 0 594 7920 0 17820 25740 
 03/10/2015 332 3 194 9990 0 28220 38739 
  
 
 $ b 
预计：
 
 
  DateTime 10Quantity 20Quantity 30数量10A安装20安装30安装总数
 03/10/2015 792 0 594 7920 0 17820 25740 
 03/10/2015 332 3 194 9990 0 28220 38739 
总计1124 3 788 17910 0 46040 64479 
  
我想要一个新的行'Totals'，它应该表示每列的总数，即加法。
 
 $ b 
代码：
 
 pre $ lt; code>< asp：GridView ID =GridViewDenominationsWiseTransactionsrunat =小号erverwidth =100％AutoGenerateColumns =False
 ShowFooter =TrueOnRowDataBound =GridViewDenominationsWiseTransactions_RowDataBoundCssClass =table table-hover table-striped table-bordered> 
<列> 
< asp：BoundField HeaderText =DateDataField =DateTimeDataFormatString ={0：dd / MMM / yyyy hh：mm}HtmlEncode =falseItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =可见桌面> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =11％/> 
< / asp：BoundField> 
 <％ - < asp：BoundField HeaderText =Conductor NameDataField =ConductorNameItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％>< / ItemStyle> 
< / asp：BoundField>  - ％> 
< asp：BoundField HeaderText =10 QuantityDataField =10QuantityItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =20 QuantityDataField =20QuantityItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =30 QuantityDataField =30QuantityItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =10 AmountDataField =10AmountItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =20 AmountDataField =20AmountItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =30 AmountDataField =30AmountItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =8％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< asp：BoundField HeaderText =GrandTotalDataField =GrandTotalItemStyle-CssClass =visible-desktop
 HeaderStyle-CssClass =visible-desktop> 
< HeaderStyle CssClass =visible-desktop>< / HeaderStyle> 
 
< ItemStyle Width =13％CssClass =visible-desktop>< / ItemStyle> 
< / asp：BoundField> 
< /列> 
< / asp：GridView> 
  

SP;

  ALTER PROCEDURE [dbo]。[ReportDenominationWiseTransaction] '03 -10-2015'，'03 -10-2015'
 @FromDate date，
 @ToDate date 
 AS $ 
选择转换（varchar（20），serverdatetime，101）'DateTime'，金额，1'Quantity'
 from票
 WHERE CONVERT（DATE，ServerDateTime）BETWEEN @FromDate和@ToDate 
）
 select *，10 * c。[10Quantity]'10Amount' （20×数量[20数量]）+（20 *数量[20数量]）+（30 * c。[20数量]'20Amount'，30 * c。[30数量]'30Amount' 30Quantity'，[30] AS'30Quantity'
 FROM 
（SELECT [30]））'GrandTotal'（
 SELECT DateTime，[10] AS'10Quantity'，[20] [日期时间]，金额，数量
 FROM myquery）p 
 PIVOT 
 （
 COUNT（[Quantity]）
 FOR [Amount] IN 
（[10]，[20]，[30]）
）AS pvt 
）as c 
 END 
  

我必须从前端完成，即gridview

解决方案

实际上你可以通过 sql找到每列的 SUM / code>本身使用 GROUPING SETS 。

查询

  SELECT [DateTime]，
 SUM（[10Quantity]）AS [10Quantity]，
 SUM（[20Quantity] ）AS [20Quantity]，
 SUM（[30Quantity]）AS [20Quantity]，
 SUM（[10Amount]）AS [10Amount]，
 SUM（[20Amount]）AS [20Amount] ，
 SUM（[30Amount]）AS [30Amount]，
 SUM（[GrandTotal]）AS [GrandTotal] 
 FROM table_name 
 GROUP BY 
 GROUPING SETS（（ [DateTime]，[10Quantity]，[20Quantity]，
 [30Quantity]，[10Amount]，[20Amount]，[30Amount]，[GrandTotal]），（））; 
  

i am trying to do column wise addition in Gridview but couldn't.

like i am filling gridview from store procedure which gives these values .

DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal
03/10/2015  792      0           594        7920        0     17820   25740
03/10/2015  332      3           194        9990        0     28220   38739

Expected:

DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal
03/10/2015  792      0           594        7920        0     17820   25740
03/10/2015  332      3           194        9990        0     28220   38739
Totals      1124     3           788        17910       0     46040   64479

i want a new row 'Totals' which should represent total for each column i.e. addition.

Code:

<asp:GridView ID="GridViewDenominationsWiseTransactions" runat="server" Width="100%" AutoGenerateColumns="False"
                        ShowFooter="True" OnRowDataBound="GridViewDenominationsWiseTransactions_RowDataBound"  CssClass="table table-hover table-striped table-bordered">
                        <Columns>
                            <asp:BoundField HeaderText="Date" DataField="DateTime" DataFormatString="{0:dd/MMM/yyyy hh:mm}" HtmlEncode="false"  ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="11%" />
                            </asp:BoundField>
                           <%-- <asp:BoundField HeaderText="Conductor Name" DataField="ConductorName" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%"></ItemStyle>
                            </asp:BoundField>--%>
                            <asp:BoundField HeaderText="10 Quantity" DataField="10Quantity" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="20 Quantity" DataField="20Quantity" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="30 Quantity" DataField="30Quantity" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="10 Amount" DataField="10Amount" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="20 Amount" DataField="20Amount" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="30 Amount" DataField="30Amount" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                            <asp:BoundField HeaderText="GrandTotal" DataField="GrandTotal" ItemStyle-CssClass="visible-desktop"
                                HeaderStyle-CssClass="visible-desktop">
                                <HeaderStyle CssClass="visible-desktop"></HeaderStyle>

                                <ItemStyle Width="13%" CssClass="visible-desktop"></ItemStyle>
                            </asp:BoundField>
                        </Columns>
                    </asp:GridView>

SP;

ALTER PROCEDURE [dbo].[ReportDenominationWiseTransaction] '03-10-2015', '03-10-2015'
@FromDate date,
@ToDate date
AS
    BEGIN   
        with myquery ([DateTime],amount,Quantity) 
        as
        (
            select  Convert(varchar(20),serverdatetime,101) 'DateTime',amount,1 'Quantity'
                 from tickets
                 WHERE CONVERT(DATE,ServerDateTime) BETWEEN @FromDate and @ToDate
        )
        select *, 10 * c.[10Quantity] '10Amount',20 * c.[20Quantity] '20Amount',30 * c.[30Quantity] '30Amount',((10 * c.[10Quantity])+(20 * c.[20Quantity])+(30 * c.[30Quantity]))'GrandTotal' from (
        SELECT DateTime,[10] AS '10Quantity', [20] AS '20Quantity', [30] AS '30Quantity'
        FROM 
        (SELECT [DateTime], amount,quantity
        FROM myquery) p
        PIVOT
        (
        COUNT ([Quantity])
        FOR [Amount] IN
        ( [10], [20], [30])
        ) AS pvt
        ) as c
    END

i must do it from front end i.e. gridview

解决方案

Actually you can find the SUM of each column by sql itself using GROUPING SETS.

Query

SELECT [DateTime],
SUM([10Quantity]) AS [10Quantity],
SUM([20Quantity]) AS [20Quantity],
SUM([30Quantity]) AS [30Quantity],
SUM([10Amount]) AS [10Amount],
SUM([20Amount]) AS [20Amount],
SUM([30Amount]) AS [30Amount],
SUM([GrandTotal]) AS [GrandTotal]
FROM table_name
GROUP BY 
GROUPING SETS(([DateTime],[10Quantity],[20Quantity],
[30Quantity],[10Amount],[20Amount],[30Amount],[GrandTotal]),());

这篇关于如何在sql中产生每列的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！