Nested SELECT Query That Also Returns COUNT From Related Table
Mar 4, 2005
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
View 3 Replies
ADVERTISEMENT
Oct 8, 2012
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate
on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
And Temp_Progression_Populate.Initial_Mapping_Complete in
(Select count(Initial_Mapping_Complete), Chapter_No
from Temp_Progression_Populate
Group by Chapter_No
Having Count(Initial_Mapping_Complete) = 1)
View 2 Replies
View Related
Mar 8, 2006
Plz help me out making a SELECT for my imagegallery.
Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all.
Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId}
In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath]
To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle]
To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select
And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID()
-But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
View 4 Replies
View Related
May 14, 2008
Hi,
Not too long ago I was looking to change a primary key in a table from one column to another, the standard accepted procedure to so procedure to do so (I was told) was:
1) drop primary key constraint from old_key_column
2)add primary key constraint to new_key_column
3) drop old_key_column (optional).
In order to carry out the first step, I needed the name of the primary key constraint, at which point I asked around for a command that would return that name (and would quite probably take as a parameter the name of the column that key is related to). The answer was :
Try this:
select name 'constraintName' from sys.indexes where object_id=object_id('<tableName>') and is_primary_key=1
Unfortunately this command never worked for me. Probably because I don't have a table sys.indexes in my DB, but I can see a table sysindexes in the system tables folder, then again that table doesn't have an object_id column, but it has an id column, finally this column only contain obscur numbers.
So I am asking you all (again) for help, do you know about a command that returns every single constraint related to a column (or a table, or both).
One more thing, I am running SQL SERVER 2000, I do know that there are graphic ways to do those very operations (in the enterprise manager or the query analyzer) and I do know those ways (that's how i got past the problem mentioned earlier), I just think that knowing how to do it programmatically would be a plus.
PS: I am a total newbie to T-SQL (and a still a beginner in SQL, so please take it easy with me )
View 6 Replies
View Related
Feb 25, 2008
HI All, I have what is most likely a simple MS SQL query problem (2005).I need to add two computed columns to a result set, both of those columns are required to provide the count of a query that contains a parmamter. (The id of a row in the result set) 3 Tables (Showing only the keys)t_Sessions-> SessionID (Unique) t_SessionActivity-> SessionID (*)-> ErrorID (Unique) t_SessionErrors-> ErrorID (1) I need to return something like (Be warned the following is garbage, hopefully you can decifer my ramblings and suggest how this can actualy be done) SELECT SessionID, (SELECT COUNT(1) AS "Activities" FROM t_SessionActivity WHERE t_SessionActivity.SessionID = t_Sessions.SessionID), (SELECT COUNT(1) AS "Errors" FROM dbo.t_Sessions INNER JOIN dbo.t_SessionActivity ON dbo.t_Sessions.SessionID = dbo.t_SessionActivity.SessionID INNER JOIN dbo.t_SessionErrors ON dbo.t_SessionActivity.ErrorID = dbo.t_SessionErrors.ErrorID WHERE t_SessionActivity.SessionID = t_Sessions.SessionID)FROM t_Sessions Any help greatfully received. Thanks
View 4 Replies
View Related
Dec 13, 2014
Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16?
Code:
Create Table #One
(
BadgeNum int,
NameOnFile varchar(1000),
hoursworked int
[code]....
View 3 Replies
View Related
Feb 15, 2006
Dear MS SQL Experts,I have to get the number of datasets within several tables in my MSSQL2000 SP4 database.Beyond these tables is one table with about 13 million entries.If I perform a "select count(*) from table" it takes about 1-2 min toperform that task.Since I know other databases like MySQL which take less than 1 sec forthe same taskI'm wondering whether I have a bug in my software or whether there areother mechanisms to get the number of datasets for tables or the numberof datasets within the whole database.Can you give me some hints ?Best regards,Daniel Wetzler
View 5 Replies
View Related
Aug 28, 2007
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
View 6 Replies
View Related
Jan 30, 2014
I'm trying to understand why I can enter a query such as:
select 5,"random"
from customers;
and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?
View 4 Replies
View Related
Apr 2, 2014
I am working on a query that seems very simple, but I just cannot seem to get it correct.
I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:
Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897
DECLARE @Members TABLE
(
MemberRowID INT IDENTITY(1,1) NOT NULL
,FirstName VARCHAR(20)
, LastName VARCHAR(20)
[Code]....
I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything.
View 4 Replies
View Related
Jul 20, 2005
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
View 1 Replies
View Related
Jul 23, 2005
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies
View Related
Sep 14, 2006
Hi Guys
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
View 20 Replies
View Related
Jan 30, 2007
hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.
SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
SELECT *
FROM #ByItemID
ORDER BY CustomLotNo
DROP TABLE #ByItemID
----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-
select customlotno, itemid, locid(
select * from tblitemdetail
where customlotno='IN28606000'
) AS T
GROUP BY customlotno, itemid, locid
~~~Focus on problem, not solution~~~
View 12 Replies
View Related
Jan 23, 2008
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
Thanks,
Lee
View 4 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jul 20, 2005
Hi.I have a table (websitehits) which holds statistics about websites.This table has a date field (datecounted). What I need is to create aquery which returns a list of dates between two date ranges (say ayear ago from today and a year from now) which only shows dates thathaven't been used in the websitehits table for a given website.For example if my table contains something like:Website Datecounted HitsSite101/01/046000Site101/02/046500Site101/03/046250Site201/03/041000Site201/04/041200Site201/05/041500So if query for ‘site1' then I'd get a list of all dates between30/11/03 to 30/11/05 with the exception of the dates 01/01/04,01/02/04 and 01/03/04.So far I've tried to do this using another table named calendar whichcontains a very long list of dates and to use this to produce the list– but I'm not getting very far.By the way I'm using sql server, an I need this query to generate alist for an asp page - so I need to pass the website name as aparameter so I guess I need to make this query as a stored procedure.Does anyone know how this can be done?
View 1 Replies
View Related
May 20, 2008
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
Part 2
2006 4 3 0
2007 1 0 7
Customer M12345 totals
2006 9 9 2
2007 1 3 8
M45678
Part 3
2007 8 4 7
2008 3 4 8
Part 4
2006 3 8 7
2007 5 6 6
Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals
2006 12 17 9
2007 14 13 21
2008 3 4 8
View 1 Replies
View Related
Jun 11, 2008
Here's my tables:
-------------------------------------------------------
tblMembers
-------------------------------------------------------
MemberID | CountryID
-------------------------------------------------------
-------------------------------------------------------
tblCountries
-------------------------------------------------------
CountryID | CountryName
-------------------------------------------------------
-------------------------------------------------------
tblOrders
-------------------------------------------------------
OrderID | MemberID | OrderTypeID
-------------------------------------------------------
-------------------------------------------------------
tblSubscriptionOrders
-------------------------------------------------------
SubscriptionOrderID | OrderID | SubscriptionPackID
-------------------------------------------------------
-------------------------------------------------------
tblSubscriptionPacks
-------------------------------------------------------
SubscriptionPackID | TypeID
-------------------------------------------------------
Here's what I'm trying to do:
1. Output each country in one column
2. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 1 in the next column
3. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 2 in the next column
My problem was that I was doing joins, and I was somehow ending up with orders where the OrderTypeID was NOT equal to 3, even though I declared it specifically in the WHERE clause.
Can someone help me with this query?
View 4 Replies
View Related
Sep 8, 2006
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter
Set counter = currentpage
If counter > lastpage
Goto task exit loop
Else perform task - (insert string into table)
Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int
DECLARE @Current string
DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage
SET @Count= dbSL.tblPaging._colPagingCount
SET @Last= dbSL.tblPaging._colLastPage
WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN
PRINT 'Hello World'
SET @Count = @Count +1
END
Any help much appreciatted.
Thanks
nme
View 1 Replies
View Related
Jul 23, 2005
Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.
View 1 Replies
View Related
Nov 5, 2014
I have two tables. Houses and Guests. The guests are the people who visited the houses tied by the HouseNo.
I want to list all of the houses which has HouseName, Address, HouseNo. Then I want to show a column for the number of guests who visited it.
The Guest table has GuestName and HouseNo. How can I do a select on all houses and then show a column of COUNT(GuestName) for all the guests with the HouseNo matching each row?
View 1 Replies
View Related
Jul 30, 2013
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
View 3 Replies
View Related
Jun 20, 2007
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
View 6 Replies
View Related
Jul 24, 2007
Hi All,
I am trying to do a nested count and cannot seem to get it to work. I do realize I cannot do this within the expression so I have been trying to use the code block (report > report properties > code tab) to do this and I have zero experience with VB.
This is for a PO delivery report. The first expression is just counting how many lines on an individual PO are late divided by total number of lines on the PO. In second expression I would like to count how many POs that have a late line on it.
Here is what I have so far.
Expression 1 with count function (this work properly):
=iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value))))
Code block:
Private Shared count as Integer
count=0
Public Function IncrementCount() As String
count = count +1
IncrementCount = CStr(count)
End Function
Expression 2
=iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value))))
& IIf(iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value)))) > 0, Code.IncrementCount(), Nothing)
The error:
[rsCompilerErrorInCode] There is an error on line 2 of custom code: [BC30188] Declaration expected.
Build complete -- 1 errors, 0 warnings.
Also "Code.IncrementCount()" is underlined with red zigzag indicating an error.
How do I get the code block to work with expression 2?
Thanks,
Blair
View 2 Replies
View Related
Nov 20, 2006
Hi friends,
I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database.
When I try to put the result (single row) in a variable I get the following error message.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.".
Pls help me
Mahe
View 4 Replies
View Related
Jun 12, 2007
Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.
View 4 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Jul 20, 2005
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies
View Related
Nov 26, 2006
How to find the average of another nested count aggregation?
For example
select count (PANUM)
from PAPER
where AVG (count(PANUM))>5
Thanks
View 16 Replies
View Related
May 14, 2008
Hiya,
I have a need for a complex SQL statement to provide reporting information, but the SQL is way over my head and although I have some of the elements, I can't seem to pull them together to create a working SQL statement.
My database structure is as outlined below:
tblDef
------
ID IRVitems
ABC A1,A2,A3,A4,A5
tblIRV
------
ID IRVvalues
001 1,5,text1,10,3
002 2,1,text2,1,3
003 2,4,text3,1,1
004 1,4,text4,4,1
005 1,2,text5,4,1
tblLabel
--------
lblID lblVALUE lblTXT
A1 1 AAA
A1 2 BBB
I currently query tblDef to get IRVitems and in turn then query the other two tables to get statistical information to present in a report (via ASP).
For each item returned from IRVitems, I would like to report the spread and frequency of different values as follows:
A1
Value Label Count Percent
1 AAA 3 60
2 BBB 2 40
and then for A2, etc (although missing out A3 as it is a text item, but I can deal with that via ASP code beforehand). I would expect to have to hit the DB for each of the IRVitems (so once for getting the data for A1, againfor A2, etc).
I was provided a great bit of code to use SUBSTRING and GROUP BY to get this data (thanks zuomin), but I didn't consider the possible text values or numbers >9 when defining what I was trying to do (to be fair, they're new requirements):
SELECT SUBSTRING(IRVvalues, 1, 1) AS Value, COUNT(ID) AS Count
FROM tblIRV
GROUP BY SUBSTRING(IRVvalues, 1, 1)
ORDER BY value
which returns like this:
Value Count
1 187
2 163
3 2
Can I do a similar query to get all the info I need in one go? I saw the articale on a user-defined 'split' function (http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home) that can be used to split up the IRVvalues string to access the position I need in the array, but I'm a little clueless on how to then embed that into a SQl statement.
Can anyone point me in the right direction please?
View 3 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
