SQL call to count the total rows in Table B for each user in Table A

I have 2 tables:

 

TableA:

Name

UserA

UserB

UserC

 

Table B:

Name               Data

UserA              xxx

UserB              asdasd

UserB              ewrsad

UserC              dsafasc

UserA              sdf

UserB              dfvr4

 

I want to count the total entries in Table B for every user in Table A.  The output would be:

 

Name               Count

UserA              2

UserB              3

UserC              1

 

I can use a Select Count statement, but I will have to make a SQL call for every user in Table A.  Also, Table A is dynamic, so the users are always changing.  Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?

0
rhess
1/17/2006 7:27:42 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
967 Views

Similar Articles

[PageSpeed] 47
Get it on Google Play
Get it on Apple App Store

SELECT
   [A.Name], count(*) as [COUNT]
FROM
   tableA A
INNER JOIN tableB B
   ON A.[Name] = B.[Name]
GROUP BY A.[Name]

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
1/17/2006 8:00:20 PM

Your SQL call worked nicely.  The last part is to reference these numbers in my .vb page, manuapulate them (maybe total them up, determine an average,...) then send the data back to my .aspx page.  I was able to easily do this in Classic ASP, bu I am a beginner in ASP 2.0.  This is a big piece of the puzzle for me.  My .aspx code is below.  Thank you.

 

<asp:GridView ID="GridView1" runat="server" CellPadding="4"

DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" AllowSorting="True">

<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<RowStyle BackColor="#EFF3FB" />

<EditRowStyle BackColor="#2461BF" />

<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

<Columns>

<asp:BoundField DataField="Assignee" HeaderText="Assignee" SortExpression="Assignee" />

<asp:BoundField DataField="COUNT" HeaderText="Open" SortExpression="COUNT" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Beringer_HeatConnectionString2 %>"

SelectCommand="SELECT A.Assignee, COUNT(*) AS COUNT FROM Assignee AS A INNER JOIN Asgnmnt AS B ON A.Assignee = B.Assignee WHERE (B.Resolution = '') GROUP BY A.Assignee">

</asp:SqlDataSource>

0
rhess
1/18/2006 5:00:00 PM
Sorry. I havent yet worked on 2.0 and the SQLDataSource's or the GrieView's. You might want to post this as a separate question in the .NET controls section where you have a better chance of getting an accurate answer.

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
1/18/2006 7:36:52 PM

ndinakar,
Thank you for the SQL call.  It's opened up a whole world for me to upgrade my code.  I have a lot of inefficient nested SQL calls.  I used your SQL call 3 times on one page to make 3 separate tables that have the same exact users.  Can you merge this into one SQL call & one table?

SelectCommand="SELECT A.Assignee, COUNT(*) AS COUNT FROM Assignee AS A INNER JOIN Asgnmnt AS B ON A.Assignee = B.Assignee WHERE (B.Resolution = '') GROUP BY A.Assignee">

SelectCommand="SELECT A.Assignee, COUNT(*) AS COUNT FROM Assignee AS A INNER JOIN Asgnmnt AS B ON A.Assignee = B.Assignee WHERE (B.Resolution = '') AND (B.DateAssign < GETDATE() - 5) GROUP BY A.Assignee">

SelectCommand="SELECT A.Assignee, COUNT(*) AS COUNT FROM Assignee AS A INNER JOIN Asgnmnt AS B ON A.Assignee = B.Assignee WHERE (B.DateAssign > GETDATE() - 30) AND (B.Resolution <> '') GROUP BY A.Assignee">

0
rhess
1/18/2006 8:13:12 PM
IF you need the results of all the 3 SQL statements, then you have to make 3 calls. You *COULD* do it all in one stored proc and use a datareader to parse through but it depends on your architecture of the page.
 
EDIT:
I just noticed this article and thought it might be of help to you:

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
1/18/2006 9:48:26 PM
Reply:

Similar Artilces:

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

row count from sql table..
i am using vb and sql .....i need a sample  row count....one  of the few things i havent learned yet...can someone help me.. i need ...  row count FROM  customerinfo ....I need the number of customers to date. THank you in advance for any help.   "War is not won on the battlefield. War is won in the minds of madmen!" beaufortsteve: row count FROM  customerinfo . is customerinfo  a database table ??if yes.. then... are you trying to get the RecordCount from customerinfo  ??if yes then... beaufortsteve:I need the number of customers...

SQL Old school *= Question select All rows from table A and rows from B
Im using SQL 2005. Love it.In the old TSQL world could useselect current.user, weekly.user, weekly.hoursfrom current, weeklywhere current.user *= Weekly.user and get all the current users from current table AND users from weekly table that have weekly hours. Somehitng like dan  12fred 24sue nullwhere sue is in the current table, but not in the weekly table.I've tried outside joins like this SELECT     dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorkedFROM         dbo.ViewCurrentUser LEFT OUTER JOIN&n...

adding html table columns from SQL table rows
I want to put data from an SQL table into an HTML table, but I want to start a new row in the HTML table when it is 5 columns wide. I have done this in classic ASP with the following code: TABLE NAME How can I accomplish the same thing with asp.net? I have created my SqlDataSource and the SelectCommand no problem, but I'm kind of stuck how to loop through the recordset. Why do you want to do it manally? Data binding is for such purposes only. Fill a DataSet or DataTable with the SqlCommand and do: GridView.DataSource = DataSet.Ta...

Wanting to take info from one SQL table and update to a seperate SQL table
Anyone have an idea of how I can take information from one table and edit it and insert into another SQL table.  I am using stored procedures and would like to continue to do so. Here is a copy of what I have so far:   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="mp_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </div> <h2><strong><font color="#C00000">Please enter a valid account number:</font></strong><...

Update row in Table A from row in Table B
Hello, I have 2 tables.... table A and table B. Ok, In a stored procedure, I pass in an int value @ID I want to get the row from Table B WHERE ID =@ID....and then UPDATE the Row in Table A that has the same ID...with the values from table B. Table A ID | Col1 | Col2 ----------------------- 2 a a Table B ID | Col1 | Col2 ----------------------- 2 b b I would like to update A.Col1 with B.Col1 and A.Col2 with B.Col2 Can someone help me out with this syntax? Thanks a lot. update tableA ...

Can I populate the dnn user tables for an exisiting SQL table?
Does anyone know of a module out there that would do this? I want to populate the users table from exisiting tables. MZNS.UserImport (Free) http://www.mikez.com/Home/Default.aspx?tabid=90SilverlightDesktop.net A framework that allows you to dynamically load Silverlight modules into resizable draggable windows....

How can i import records from a sql server table in project1 to another sql server table in project2
Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same. One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to ...

Getting table records count with SQL statement VB.net
I have a table named USERS with a column named USERNAME. I am trying to get the count and cannot seem to get any results back. Code I've tried. cmd2.CommandText = "SELECT USERNAME AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("RETURNCOUNT") ************Did not return any*************  ******************************************************************************************************************************* cmd2.CommandText = "SELECT COUNT(0) AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("R...

how can i add row to table of sql with vb.net code?
how can i add row to table of sql with vb.net code?  You can use System.Data.SqlClient classes :  Using con As New SqlConnection(" your connection string goes here ") Dim com As New SqlCommand("Insert into Person (id,name) values(@id,@name) ", con) com.Parameters.AddWithValue("@id", 1) com.Parameters.AddWithValue("@name", "Nomi1") com.Connection.Open...

SQL Table Rows
How is it possible to cycle through an SQL table row by row, without having to increment an ID by 1 in a while loop and go SELECT * FROM table WHERE id=$id ? And how can I find out how many rows are in the table? Dan Dear Dan, I don't know if I understood what you really want. Therefore I will try to help, ok. I use Postgres as Database. You can find out how many rows have in a table after you run a SQL like this. #!/usr/bin/perl use Pg; $db = Pg::connectdb("dbname=database"); # This is a way to do it. $result = $db->exec("SELECT ...

line break problem, I used the DetailsView to save an article to sql table, then I use label to read out from sql table, but couldn't break line.
Here is my code: <asp:Label ID="Label" runat="server" Text='<%# Eval("ServiceBody") %>'></asp:Label>Life is not easy but beautiful! use <asp:Label ID="Label" style="word-wrap:normal" runat="server" Text='<%# Eval("ServiceBody") %>'></asp:Label> and fix the widht of the colum in which you have placed this lable. like width=150px    If this post helps you, please mark it as Answer.Cheers,Muhammad Rizwan JavedSr .Net DeveloperAim 168, Dubai, UAE. Use ...

T-Sql insert from table to table
Ok, need a little input on how to aproach this problem.Transfering data from one table to another.The first statement was easy...INSERT INTO myTable(ID)     SELECT ID      FROM someTableBut now, need to get the rest of the data - row by row.Selecting table columns:userID, objID0, objID1, objID2........ objID19 Also status 0 - 19, max 0 - 19Inserting table columns......userID, objID, courseID = "something not in selecting table",  status, maxAs you can see I am improving the database. (not my creation  )So, the selecting table r...

Export sql table data into sql
 Dear friends ,   give any ideas for sql table data export into excel in asp.net c#   Hi  you can use import /export in sql server enterprise manager, other wise in c# you can bind  sql datas into gridview then convert into excel using following method  string filename = "ExcelFileName.xls"; HttpResponse response = HttpContext.Current.Response; // clean up the response.object response.Clear();response.Charset = ""; // set the response mime type for excelresponse.ContentType = "application/vnd.ms-excel"; response.Ad...

Web resources about - SQL call to count the total rows in Table B for each user in Table A - asp.net.sql-datasource

Count - Wikipedia, the free encyclopedia
The word count came into English from the French comte , itself from Latin comes —in its accusative comitem —meaning "companion", and later "companion ...

Black Friday Death Count
People are nuts. ∞ Read this on The Loop

Migrant crisis: Open-door policy queried as Germans lose count of arrivals
Finance Minister warns of a refugee &quot;avalanche&quot; that &quot;could become pretty bad for us all&quot;.

Make every day count
THE parents of a six-year-old boy killed in a rally accident in the South West have urged families to cherish every moment they have with loved ...

Monarch butterfly count expected to rebound in Mexico
After a staggering decline over the past two decades, the population of the iconic monarch butterfly is rebounding thanks to transnational cooperation. ...

Migrant crisis: Open-door policy queried as Germans lose count of arrivals
Finance Minister warns of a refugee &quot;avalanche&quot; that &quot;could become pretty bad for us all&quot;.

Comcast launches streaming TV service that doesn’t count against data caps
... areas, with plans to bring it to Comcast's entire cable territory by early next year. We asked Comcast today if Stream TV usage will count ...

Former Baltimore officer accused of animal cruelty in dog's death found not guilty on all counts
A Baltimore circuit judge found a former city police officer not guilty of animal cruelty charges Thursday after he slit a dog's throat last ...

Let us count the ways of Patriots’ dominance
Offense, defense, special teams — the unbeaten Patriots have shown no weak spots at the midway point.

FCC Chair: Video Streaming That Doesn’t Count Against Your Data Caps Is “Innovative” And “Highly Competitive” ...
... for example, just launched its Binge On plan, offering subscribers “optimized” versions of more than 20 streaming services that won’t count ...

Resources last updated: 11/26/2015 2:53:09 PM