如何在sql中产生每列的总和?(How to produce sum of each column in sql?)

322 IT屋

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]),());

我正在尝试在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 ="GridViewDenominationsWiseTransactions"runat ="小号erver"width ="100%"AutoGenerateColumns ="False"
ShowFooter ="True"OnRowDataBound ="GridViewDenominationsWiseTransactions_RowDataBound"CssClass ="table table-hover table-striped table-bordered">
<列>
< asp:BoundField HeaderText ="Date"DataField ="DateTime"DataFormatString ="{0:dd / MMM / yyyy hh:mm}"HtmlEncode ="false"ItemStyle-CssClass ="visible-desktop"
HeaderStyle-CssClass ="可见桌面">
< 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>
< /列>
< / asp:GridView>



SP;



 < code> 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]),());

本文地址:IT屋 » 如何在sql中产生每列的总和?