Solved

Need sql query to total rows and display in gridview

Posted on 2007-11-16
18
467 Views
Last Modified: 2012-06-27
Hello, I have a billing table. Each entry in the table is accociated to a clientID. There could be multiple rows with the same ClientID. I need a sql query (I'm using ms sql 2005) that will add all the rows where the clientID=@ClientID and only display one record in a gridview, with the total of a "money" column. Example code I have  is this:

Protected Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        SqlDataSource1.SelectCommand = "SELECT * FROM Billing WHERE ............."
        SqlDataSource1.DataBind()

    End Sub

Billing Table structure:
InvoiceID, int
AmountBilled, money
AmountPaid, money
0
Comment
Question by:mlg101
  • 10
  • 8
18 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 20300435
"Select ClientID, ClientName, sum(BillingAmt) ClientTotal
from Billing
where ClientID=" + @ClientID +
" group by ClientID, ClientName"


 
0
 
LVL 1

Author Comment

by:mlg101
ID: 20301234
Here is my sql from your example and it get an error: "conversion failed when converting the varchar value ' + @InvoiceID + ' to datatype int"

I thought invoiceid was datatype int already?

"SELECT InvoiceID, BillingType, sum(Amount) BillingTotal FROM Billing WHERE InvoiceID= ' + @InvoiceID + ' Group by InvoiceID, BillingType"
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20303381
What happened to the idea of summing by ClientID? Nevertheless, the problem is that you need to resolve @InvoiceID in VB, not in SQL:

="Select InvoiceID, BillingType, sum(AmountBilled) BillingTotal FROM Billing WHERE InvoiceID=" &  THE_INVOICE_NO_VARIABLE_GOES_HERE    & " Group by InvoiceID, BillingType"

 

0
 
LVL 1

Author Comment

by:mlg101
ID: 20310198
I was saying that the billing table has clientID as a column. So the clientID matches with the client table.

Anyway, I tried what you said again and I get the same error. Except I put @InvoiceID where you said "the invoice no variable goes here. Im lost I guess
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20310231
>Except I put @InvoiceID where you said "the invoice no variable goes here.

I'm afraid you missed the whole point.  "@InvoiceID" is an SQL parameter.   But SQL doesn't have anyway to know what InvoiceID you are interested in.

Instead of "@InvoiceID" put in the actual InvoiceID. (Technically, after converting it to a string).  You should be able to get the InvoiceID from a VB variable or from a control on your form.
 
See what I mean?  The command string should look like this:

Select InvoiceID, BillingType, sum(AmountBilled) BillingTotal FROM Billing WHERE InvoiceID=12345 Group by InvoiceID, BillingType

     
0
 
LVL 1

Author Comment

by:mlg101
ID: 20310323
I see what you are saying, but I want to select all invoiceID rows. and total them, grouped by clientname, which I get by the clientID in the billing table.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20310369
I'm confused, you said:

I need a sql query (I'm using ms sql 2005) that will add all the rows where the clientID=@ClientID and only display one record in a gridview

So, how many records do you want to see in the gridview?  

This returns multiple rows, grouped by client:
"Select ClientID, sum(AmountBilled) BillingTotal FROM Billing Group by ClientID"

This returns one row for a specific client:
"Select ClientID, sum(AmountBilled) BillingTotal FROM Billing where clientID=" & YourClientIDVariableGoesHere

0
 
LVL 1

Author Comment

by:mlg101
ID: 20310488
Sorry for the confusion. I want to return one row for each client. So if a client has 3 rows, it will add those rows and put the sum in one row. The same for each client who has rows in the table. So I dont have a specific clientID either. I want to sum up all clients into one row for each. Does that make more sense?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20310577
>Does that make more sense?

Yes, that's very easy:

"Select C.ClientName, sum(B.AmountBilled) BillingTotal FROM Billing B
inner join ClientTable C on C.ClientID=B.ClientID
Group by C.ClientName"


Note: Replace "ClientTable" and "ClientName" with your table and column name.


0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:mlg101
ID: 20310635
OK, I did that and I got results, but it did not combine the client name. It just listed each client. Here is my code in more detail. I was trying to strip it down to make it simpler, but here it is:

SqlDataSource2.SelectCommand = "SELECT Billing.InvoiceID, Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID, SUM(Billing.Amount)as Amount, SUM(Billing.AmountPaid)as AmountPaid FROM Billing Left Outer Join Client ON Billing.ClientID = Client.ClientID and Billing.BillClient = 1 Left Outer Join Company ON Billing.ClientID = Company.CompanyID and Billing.BillClient = 0 WHERE ((ClientFirmName LIKE '%" & Textbox1.Text & "%' OR ClientShortName LIKE '%" & Textbox1.Text & "%') Or (CompanyName LIKE '%" & Textbox1.Text & "%')) Group by Billing.InvoiceID, Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID"
0
 
LVL 1

Author Comment

by:mlg101
ID: 20310651
do i need to make any adjustments in my gridview code? To allow a sum? Here is my gridview code:


<asp:GridView id="GridView2" runat="server" ShowFooter="true" BackColor="White" AllowSorting="True" AutoGenerateColumns="False" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" DataKeyNames="InvoiceID" DataSourceID="SqlDataSource2" GridLines="None" Width="864px">

                    <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />

                    <Columns>

                        

                        <asp:TemplateField HeaderText="Client/Company Name" SortExpression="ClientID">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox35211" runat="server" Text='<%# Bind("ClientID") %>'></asp:TextBox>

                                <asp:TextBox ID="TextBox22511" runat="server" Text='<%# Bind("CompanyID") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:hyperlink ID="Label5234241" NavigateUrl='<%# Eval("clientID", "ClientAdminEdit.aspx?clientNumber={0}&type=" + Request.QueryString("type")) %>' runat="server" Text='<%# Bind("ClientFirmName") %>'></asp:hyperlink>

                                <asp:hyperlink ID="Label5523421" NavigateUrl='<%# Eval("companyID", "CompanyAdminEdit.aspx?companyNumber={0}&type=" + Request.QueryString("type")) %>' runat="server" Text='<%# Bind("CompanyName") %>'></asp:hyperlink>

                            </ItemTemplate>

                        </asp:TemplateField>

                        

                        <asp:TemplateField HeaderText="Address" SortExpression="ClientAddress">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox41" runat="server" Text='<%# Bind("ClientAddress") %>'></asp:TextBox>

                                <asp:TextBox ID="TextBox441" runat="server" Text='<%# Bind("CompanyAddress") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:Label ID="Label51" runat="server" Text='<%# Bind("ClientAddress") %>'></asp:Label>

                                <asp:Label ID="Label551" runat="server" Text='<%# Bind("CompanyAddress") %>'></asp:Label>

                            </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="City" SortExpression="ClientCity">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox51" runat="server" Text='<%# Bind("ClientCity") %>'></asp:TextBox>

                                <asp:TextBox ID="TextBox541" runat="server" Text='<%# Bind("CompanyCity") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:Label ID="Label61" runat="server" Text='<%# Bind("ClientCity") %>'></asp:Label>

                                <asp:Label ID="Label621" runat="server" Text='<%# Bind("CompanyCity") %>'></asp:Label>

                            </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="State" SortExpression="ClientState">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox61" runat="server" Text='<%# Bind("ClientState") %>'></asp:TextBox>

                                <asp:TextBox ID="TextBox651" runat="server" Text='<%# Bind("CompanyState") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:Label ID="Label711" runat="server" Text='<%# Bind("ClientState") %>'></asp:Label>

                                <asp:Label ID="Label731" runat="server" Text='<%# Bind("CompanyState") %>'></asp:Label>

                            </ItemTemplate>

                        </asp:TemplateField>

                        

                        <asp:TemplateField HeaderText="Amount Billed" SortExpression="Amount">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox3631" runat="server" Text='<%# Bind("Amount") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:Label ID="Label36771" runat="server" Text='<%# Bind("Amount") %>'></asp:Label>

                            </ItemTemplate>

                            

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Amount Paid" SortExpression="AmountPaid">

                            <EditItemTemplate>

                                <asp:TextBox ID="TextBox9331" runat="server" Text='<%# Bind("AmountPaid") %>'></asp:TextBox>

                            </EditItemTemplate>

                            <ItemTemplate>

                                <asp:Label ID="Label1321" runat="server" Text='<%# Bind("AmountPaid") %>'></asp:Label>

                            </ItemTemplate>

                            

                        </asp:TemplateField>                        

                                 

                        <asp:HyperLinkField HeaderText="Order #" DataTextField="OrdersID" DataNavigateUrlFields="OrdersID"  DataNavigateUrlFormatString="Control_Page_Copy2.aspx?NewJobNumber={0}"/>

                        

                        

                    </Columns>

                    <RowStyle BackColor="#DEDFDE" ForeColor="Black" />

                    <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />

                    <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />

                    <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />

                </asp:GridView> 

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 20310694
The problem is because you have InvoiceID in your Select statement.   I keep taking out invoiceID and you keep putting it back in.  You cannot sum all invoices for a client and group by invoiceID at the same time.  You cannot even show the invoice number.  Suppose the client summary record has 3 invoices, then which one did you want to see?

Also, I don't understand you outer joins to both company and client table. It's very odd to me that billing.clientID can sometimes reference the client table and other times reference the company table.  


Try this:

SqlDataSource2.SelectCommand = "SELECT Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID, SUM(Billing.Amount) as Amount, SUM(Billing.AmountPaid) as AmountPaid
FROM Billing left outer join Client ON Billing.ClientID = Client.ClientID
Left Outer Join Company ON Billing.ClientID = Company.CompanyID and Billing.BillClient = 0 WHERE Billing.BillClient = 1 and
 ((ClientFirmName LIKE '%" & Textbox1.Text & "%' OR ClientShortName LIKE '%" & Textbox1.Text & "%') Or (CompanyName LIKE '%" & Textbox1.Text & "%')) Group by Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID"





0
 
LVL 1

Author Comment

by:mlg101
ID: 20310741
If I take out InvoiceID, I get an error "databinding: 'system.data.datarowview' does not contain a property with the name 'invoiceID'

Plus the reason why i use the Left outer join is because the billing table has one column called ClientID. The clientID either references the client table or the company table, depending on whether billclient = 1 or 0.

So how do i get it to stop giving me that error about invoiceID?
0
 
LVL 1

Author Comment

by:mlg101
ID: 20310758
I copied and pasted the entire code example in your last post and I still get the error  about databinding does not contain property with the name of invoiceid.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 20310982
>So how do i get it to stop giving me that error about invoiceID?

That's a problem with your GridView control.  you need to modify it to remove any references to invoiceID.

Also,  I introduced a problem in your join by moving one of the conditions that I didn't understand.  Please change to this:

SqlDataSource2.SelectCommand = "SELECT Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID, SUM(Billing.Amount) as Amount, SUM(Billing.AmountPaid) as AmountPaid
FROM Billing left outer join Client ON Billing.ClientID = Client.ClientID and Billing.BillClient = 1 and
Left Outer Join Company ON Billing.ClientID = Company.CompanyID and Billing.BillClient = 0 WHERE
 ((ClientFirmName LIKE '%" & Textbox1.Text & "%' OR ClientShortName LIKE '%" & Textbox1.Text & "%') Or (CompanyName LIKE '%" & Textbox1.Text & "%')) Group by Client.ClientFirmName, Company.CompanyName, Company.CompanyID, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Client.ClientState, Company.CompanyCity, Company.CompanyState, Billing.OrdersID, Billing.ClientID"


0
 
LVL 1

Author Closing Comment

by:mlg101
ID: 31409614
Thank you! That worked perfectly.
Can you tell me why my WHERE clause does not work now, or should I start a new post? It works fine on another other sql query I use.
0
 
LVL 1

Author Comment

by:mlg101
ID: 20313826
Can you tell me why my WHERE clause does not work, or should I start a new post for that? It works fine in another sql query I use on the same page.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20314456
>Can you tell me why my WHERE clause does not work

By "not working" what do you mean?  Do you get all Clients, no clients, or the wrong clients?

Add a temporary msgbox to display sqlDataSource2.SelectCommand after constructing it.  Post the exact SQL that is produced and I'll have a look at it.
 




0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now