i can do 3 separate select count(*) if these were stored in a table but the result set was created using joins in the Query Analyser. I can also achieve the above result using unions with 3 separate queries with different conditions but this way i have to join the tables 3 times in each query and this is slowing down the query.
Please let me know if there is some other way i can achieve this.
Hi,I have 2 tables, Mail_subject and Mail_Usage.Mail_Subject contains the subject, body and some other bits of info.CREATE TABLE [Waterford_MailSubject] ([ID] [int] NOT NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailCategory] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[MailCreateDate] [smalldatetime] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOMail_Usage records the amount of times a certain mail was sent.CREATE TABLE [Waterford_MailUsage] ([ID] [int] NOT NULL ,[RepScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MemberScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[TimeDate] [smalldatetime] NULL ,) ON [PRIMARY]GOThey are joined by Subject (not my idea, its a DB ive inherited).What i need is to get the Mail Subject and the number of times thatMail was sent. Ive Joined them using an INNER JOIN which gave me acount of the number of times each one occoured except for Mails thathave not been used. I need to get zero as the count of Mails notsent. Ive tried a LEFT OUTER JOIN but it didnt work either.Can someone point out what i need to do ?
I have the following table Region Table ID ParentID RegionName
RestaurantTable ID RestaurantName RegionID
What i tried to do is count the number of restaurants by specific regionname. My current query is Select RegionID, RegionName, count(*) as RestaurantNo From Region Inner Join Restaurant On Region.ID = Restaurant.RegionID
However I only get the results below RegionID RegionName RestaurantNO 1 A1 0 2 A1.1 2 3 A1.2 1 4 A1.3 0
Where A1.1 , A1.2, and A1.3 are children of A1 in Region table The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3 Could anyone help me to solve this problem. Thank you
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015.Â
I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
Hi, I am trying to cycle through a table and trigger an event based on some critera. I am not sure how to do it. I am a classic VBA guy, so I might be way off: Dim myConnection As SqlConnection Dim myCommand As SqlCommand myConnection = New SqlConnection("MY SQL DATA SOURCE") myConnection.Open()
myCommand = New SqlCommand("SELECT * FROM history", myConnection) Dim dr = myCommand.ExecuteReader() Dim i As Integer = 1
While dr.read() i = i + 1 ' HOW DO I CYCLE THROUGH THE ROWS AND ASK IF A FIELD EQUALS A VALUE ' field name = "Tail"
hi I have 3 tables:Article,Source and File. Each article can have multiple filenames. The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory The fields of table Source are:SourceID,SourceName the fields of Table File:ID,ArticleID,Filename Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate I obtain a number of recordsets from the above query. Then for each recordset(Rs1),Let's say for the first recordset Rs1.MoveFirst I want to apply this query: Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID I want from the above query to have the Filenames corresponding to each Article because in my VB form I have 2 command buttons:one gives me the article's definition(Date,Source) and the other gives me the Filename of the current recordset(Article) The above SQL syntax is it correct?
SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName FROM Menu ORDER BY MenuDate DESC
This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).
Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!
I am using classic ASP. Records are grouped together by a GroupUnique number. Some groups are small with about 10 records, othere are larger at about 160.
For each record, I have about 50 columns of data that I need to display on a webpage. Because the 50 columns don't easily fit on the one page, I create two tables, each displaying 26 columns, the first columnn being an ID column. Due to the size of groups, sometimes the tables are very large - and when they get too big it overloads the server.
I think the main problem is the two tables. I use two recordsets (one of them is shown below - although instead of a SELECT * I do in fact name the columns needed for each table). I have to use two because the Recordsets don't like me using the ID column again - once it is used it is gone.
Is there a better way to store all of this information so that I can just use the one recordset? Possibly in an array? Is there a more efficient way of getting the data?
<% Dim Recordset4__MMColParam1 Recordset4__MMColParam1 = "1" If (Scramble.Fields.Item("GU").Value <> "") Then Recordset4__MMColParam1 = Scramble.Fields.Item("GU").Value End If %>
I am having some problems. I'm pretty new to sql and really dont know how to achieve more than the basic selects etc.,
My problem is that I have a recordset on one page http://www.photoghetto.com/photo-images/animals.asp that returns the results of all the images in one category. In this case it's animals and wildlife.
The user can click on any image and go to a page that shows a larger detail version of the image. http://www.photoghetto.com/photo-images/animals-photo-detail.asp
What I do is post the ProductID number to this page so that the selected thumb is shown. So for exmaple for the image of the wild cat it is http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6
My problem is that on the animals-photo-detail.asp the user has to be able to "scroll" through all the images from the category.
I.e should be able to hit the previous image button and see the stallion image, or the next button to see the butterfly etc., and thus scroll through all the images on this age if he/ she wishes to.
I understand the principles of having a results page and then being able to click on one of the results and getting a detail page. Such as I have it here. With the http://www.photoghetto.com/photo-images/animals.asp as a results page listing all the results of the category, and then when the user clicks on one of the results, goes to a detail page, for example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6.
My problem is that what I need is the recordset from the listing page to function on the detail page so that the user can scroll through the results in the same order that they were on the results page.
I have searched now for a couple of days online and every tutorial I find shows the same structure. Results Page > Detail Page.
The sql i am using on the detail page is simply,
<% Dim photos_rs__MMColParam photos_rs__MMColParam = "1" If (Request.QueryString("ProductID") <> "") Then photos_rs__MMColParam = Request.QueryString("ProductID") End If %>
So I understand why it will only display the one result since thats the detail page.
Is it possible to be able to scroll through the results using the previous and next buttons as I have setup in the display on the http://www.photoghetto.com/photo-images/animals-photo-detail.asp page. For example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=6.
Since the resutls are gathered from across the database its not possible to have a href tage that does a <<< http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6 -1) or a >>> http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6+1).
I guess it has to be something with a recordset index but does anyone know how to do it? And does anyone have the ability to help me do it?
My boss is kicking my butt now to get this thing online at some point today, and I'm turning to you guys for help if possible.
I'm sorry if this is a stupid question. I've really ran out of ideas.
This asp code displayes records in a combo box:<%openDB()call updateDB("usp_retrieveOptions",rs)if not rs.eof then%><tr><td width="66">Options</td><td width="137"><select name="select1" class="TextField1"><%i = 0do while not rs.eofif rs(0) <> Arr(i) thenresponse.write "<option value=" & rs(0) & ">" & rs(1)i = i + 1end ifrs.movenextloop%></select></td></tr><tr><td colspan="2" width="206"><center><table width="71" border="0" cellspacing="3" cellpadding="0"height="33"><tr><td width="9" height="30"><input type="submit" name="Assign" value="Assign"></td></tr></table></center></td></tr><%end ifcloseRS()closeDB()%>The call updateDB("usp_retrieveOptions",rs) invokessub updateDB(SQL,rs)set rs = objConn.Execute(SQL)end suband my usp_retrieveOptions stored procedure:create procedure usp_retrieveOptionsAS SET NOCOUNT ONSELECT OptionID, Description FROM OptionsReturnGOnow in my asp code when I try response.write rs.RecordCount I am getting-1 all the time. How do I solve the problem. Your help is kindlyappreciated.Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
Im doing a select that should retrieve a name from one table and display thenumber of correct bets done in the betDB (using the gameDB that has info onhow a game ended)I want the "MyVAR" value to be used in the inner select statement withouttoo much hassle. As you can see im trying to get the "MyVAR" to insert inthe bottom line of the code.Whats the quick fix to this one..?Thanks in advance :-)---------- code begin ----------select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)from GamesDBinner join GameBetDBon GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =GamesDB.awaygoalinner join memberDBon memberDB.memberID = GameBetDB.memberIDwhere GamesDB.gameID=GameBetDB.gameIDand GameBetDB.memberID= MyVAR ) as wins from memberDB---------- code end ----------
I use SQL 2000 I have a Column named Bool , the value in this Column is 0�0�1�1�1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
I am connecting to the database as following: set con = server.createobject("adodb.connection") con.open "connectionstring" set rs = con.execute("select * from tablename")
I am able to display the records but if I want to give adopenstatic to the above connection, how can I do so?
I have some problems to edit a recordset in an ActiveX DTS using Vbscript. Here is an example of the script :
dim varsql, varset, varconn set varconn = CreateObject("ADODB.Connection") set varset = CreateObject("ADODB.Recordset") varconn.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Enregistrement3;user id = 'sa';password=''"
if varset.recordcount >0 then do while varset.eof varset.edit ......................... varset.update varset.movenext loop end if varset.close varconn.close
Does someone see what is wrong ? I allways get -1 for the varset.recordcount ! (I checked there are some records into the table). I don't think it is a problem of user's right as it works with the same user configuration using a SQL action requery. If I modify the open statement like this : varset.Open varsql, varconn, 1 varset.recordcount contains the good number of records but the recordset is read noly and can't be modified...
<!--- Update the DISTANCE field on STORE table ---> <cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#"> exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#' </cfquery> </cfloop>
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records. I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time. I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410. The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start. This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
