Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Need sql query to total rows and display in gridview

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
mlg101
Asked:
mlg101
  • 10
  • 8
1 Solution
 
dqmqCommented:
"Select ClientID, ClientName, sum(BillingAmt) ClientTotal
from Billing
where ClientID=" + @ClientID +
" group by ClientID, ClientName"


 
0
 
mlg101Author Commented:
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
 
dqmqCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
mlg101Author Commented:
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
 
dqmqCommented:
>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
 
mlg101Author Commented:
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
 
dqmqCommented:
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
 
mlg101Author Commented:
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
 
dqmqCommented:
>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
 
mlg101Author Commented:
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
 
mlg101Author Commented:
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
 
dqmqCommented:
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
 
mlg101Author Commented:
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
 
mlg101Author Commented:
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
 
dqmqCommented:
>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
 
mlg101Author Commented:
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
 
mlg101Author Commented:
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
 
dqmqCommented:
>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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now