Need sql query to total rows and display in gridview

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


 

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"

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"

 

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

>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

     

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.

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

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?

>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.

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"

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> 

Select allOpen in new window

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"

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?

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.

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.

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.

>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.