Solved

Need sql query to total rows and display in gridview

Posted on 2007-11-16
18
464 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

18 Experts available now in Live!

Get 1:1 Help Now