Select Subquery To Return COUNT

Oct 28, 2006

I have 2 tables, Jobs and Categories.
Each job belongs to a category. At present, I am returning all categories as follows:
SELECT categoryID, categoryName FROM TCCI_Categories
What I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:
Engineering(5)
Mechanical(10) etc.
My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:
SELECT c.categoryID, c.categoryName, COUNT(j.jobID)
FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) j
WHERE j.categoryID = c.categoryID
GROUP BY c.categoryID, c.categoryName, j.jobID
This is the output when I run the query:
categoryID categoryName  Column1
----------------  ----------------------  ------------------------------
32              Engineering     1
How would I fix this?

View 2 Replies


ADVERTISEMENT

How To Return 1 If Select Count(*) Is &> 0

Apr 23, 2008

I'm ashamed! I'm stuck on something so simple....

I want to return a value of 1 if count(*)>0 AND 0 if COUNT(*) is 0

I have currently have this below, but isn't there a better way?

SELECT cnt=CASE WHEN (SELECT COUNT(*) FROM MyTable)>0 THEN 1 ELSE 0 END

(The full code is rather more complex than this, but the problem is the same)

Any suggestions welcome.

Cheers!
Mark

View 5 Replies View Related

Qusetion About Return Values From EXEC('select Count(*) From XTable')

Aug 23, 2006

Hello everybody!

As the topic:

Can i get the value "count(*)" from EXEC('select count(*) from xTable')

Any helps will be usefull! Thanks!

View 6 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

Return Max Value From SubQuery

Apr 21, 2007

Hi,I'm trying to outer join to a maximum date value using a subquery inorder to return company information and the last activity dateassociated. The basic working "sub" query is:SELECT actcomp.company_id, MAX(act.due_date)FROM oncd_activity_company AS actcomp, oncd_activity AS actWHERE actcomp.activity_id = act.activity_idGROUP BY company_idThe overall (abbreviated) query I'm trying to insert this select intois:SELECT oncd_company.company_id,oncd_company.company_name,act.due_dateFROM oncd_companyLEFT OUTER JOIN oncd_activity_company ON (oncd_company.company_id =oncd_activity_company.company_id)LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)FROM oncd_activity_company AS actcomp, oncd_activity AS actWHERE actcomp.activity_id = act.activity_idGROUP BY company_id) ON(oncd_activity_company.company_id = actcomp.company_id)I'm receiving an "invalid syntax near keyword ON" error (highlightappears on the period in "oncd_activity_company.company_id").Any help would be appreciated!Thanks,Chris.

View 1 Replies View Related

Sql Complicated Count Subquery

Oct 19, 2004

I have a table that has attendance for programs. What I need to get is the number of days each month that any program had an entry for. What I'm trying to do is ensure that an active program had all entries for that particular month.

I can do this query and subquery in Access but not in sql. Here's the Access queries:

1)SELECT Cen_DailyReport.PgmID, DatePart("m",[date]) AS Mo, DatePart("d",[date]) AS Dy
FROM Cen_DailyReport
WHERE (((DatePart("yyyy",[Date]))=2004))
GROUP BY Cen_DailyReport.PgmID, DatePart("m",[date]), DatePart("d",[date])
HAVING (((Cen_DailyReport.PgmID)="dyad"));

Then to get the actual day count per month, the next query is:

2) SELECT Query11a.PgmID, Query11a.Mo, Count(Query11a.Dy) AS CountOfDy
FROM Query11a
GROUP BY Query11a.PgmID, Query11a.Mo;

and this query aggregates the data from the 1st query. I'm wanting to do this in SQL.

In the long run, I want to take that count in the query and compare it with the actual number of days in a month to see if any entries are missing (like a program not being entered at all on any given day that they should have had entries for)

Any help would be appreciated.

View 1 Replies View Related

Count Subquery In Where Clause (3.1)

Apr 18, 2008

I am attempting to do something like this...

select * from applebucket ab where 2 = (select count(id) from apples ap where ap.applebucket_id = ab.id)

SSCE 3.1 throws the following

There was an error parsing the query. [ Token line number = x,Token line offset = y,Token in error = SELECT ]

Any help would be greatly appreciated!

View 3 Replies View Related

Top Percent In Subquery Return Various Rows

Apr 14, 2006

Hi,

We are running sql server 2005 standard edition. In the query below, it has top percent clause in the subquery and returns various rows, 2, or 3, or 4 rows each times when running manually one after another. Does anyone have an explaination?

SELECT
dbo.WOB_STEP.STEP_UID
FROM
dbo.WOB_STEP
INNER JOIN dbo.CURRENT_DATES CD1 ON (dbo.WOB_STEP.CD_UID=CD1.CD_UID)
WHERE
( dbo.WOB_STEP.STEP_UID IN (SELECT TOP (50) PERCENT WITH TIES STEP_UID FROM dbo.vWOB_STEP_RANDOM WHERE OPERATOR_NAME = 'Jennelyn Llobrera' AND VENDOR_RETURN_FLAG = 1
ORDER BY RandomID) )

Thanks very much in advance !!!

View 3 Replies View Related

Subquery Headache With Count And GroupBy

Nov 23, 2005

I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...

View 3 Replies View Related

Return Subquery Rows As One Delimited Column

Oct 26, 2006

I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?

View 8 Replies View Related

Transact SQL :: Update Unique Records Count For Subquery?

Sep 2, 2015

updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X' 

[Code] ....

Current Result

FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER

[code]....

View 4 Replies View Related

Need To Know How To Return Row Count From SP

May 16, 2007

How can I tell if table "CompanyInfo" has any rows?  Below is the partial code behind and the SP I am running to fill CompanyInfo;
PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT  C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM    tblStoreSubmitWHERE C_ID = @CompanyIDRETURN
 SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try    {       da.Fill(ds, "CompanyInfo");

View 1 Replies View Related

Count Of Sum - Return Value

Nov 16, 2012

I'm trying to do a sum of a count. I'm running the below query. I want a sum of BrowserCount however I want to return the datatable which is returned by this query. Is that possible should I be using a return value? Or is there another way?

Select UA.Browser_ID, B.Browser_Name_NM, count(B.Browser_Name_NM) as BrowserCount From llc.User_Agent_TB as UA
Left Join llc.Browser_TB as B on UA.Browser_ID = B.Browser_ID
Group by B.Browser_Name_NM, UA.Browser_ID
Order by BrowserCount DESC

View 3 Replies View Related

Return Count(*) = 0?

Jan 22, 2008

This seems pretty straightforward, but I can't seem to figure out how to do this.

So I'm looking at the last fifteen days of data and I want to see how many data points are below a certain value, and the average of those data points. This is the query I'm currently using:




Code Snippet
SELECT COUNT(*) Below, TRUNC(t.THETIME) Day, ROUND(AVG(t.THEVALUE),2) ValAvg
FROM MyTable t
WHERE t.THEVALUE <= 50 AND t.THETIME >= TRUNC(SYSDATE-14)
GROUP BY TRUNC(t.THETIME)
ORDER BY TRUNC(t.THETIME)




I later have to compare the result of this query to the same query without the 't.THEVALUE <= 50' condition. The problem I'm having is that if there are no values below 50 there is no '0' with that date. Is there a way to return 0's for days with no values below 50 that meet the time constraints? Thanks.

View 2 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Problem Getting COUNT() To Return The Value I'm After

Oct 8, 2004

Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.

Table Def: tblOfficePivot
-----------------------
key <- PK ID
officeLink <- Link to office record
employeeLink <- link to employee record.

I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)

I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:

SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250

The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:

theTotal
--------
1. 50
2. 123

What I want instead is the total number of offices, in this case 2.

Is there a way to do this without the COMPUTE clause?

Thanks for any suggestions, this one is driving me crazy.

View 3 Replies View Related

Return Count Instead Of Rows?

Oct 23, 2013

The following query returns 2142 rows which is correct.

Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0

How can I convert this query so that it returns just the count of 2142?

View 4 Replies View Related

Return Column Name And Count

Nov 3, 2013

I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:

The following query:

SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID

Returns this result:

Category
BMS
BMS
Water
BMS
BMS
Air

And the following query:

SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID

Returns this result:

AssetQty
4
1
1

I need to have both of those results returned, as a single result. Such as:

Category AssetQty

BMS 4
WATER 1
AIR 1

However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:

Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.

View 5 Replies View Related

Select Without Subquery

Apr 3, 2008

Hi,

I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.

I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.

Thanks,
Chamal.

View 6 Replies View Related

Select From Subquery

Jul 23, 2005

Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD

View 2 Replies View Related

Stored Procedure - Return Count

Jun 16, 2005

Having a little trouble with this sp... just need to return the count.....CREATE  PROCEDURE dbo.getTotalObjectives @courseId   VARCHAR(20) ASBEGIN DECLARE @errCode     INT
  SELECT count(*)  FROM   objstructure  WHERE  courseId  = @courseId
 SET @errCode = 0  RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath

View 4 Replies View Related

Return Unique Count (Two Values)

Sep 6, 2013

I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.

ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04

I need two results.

Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.

View 3 Replies View Related

Select Subquery Returned More Than 1 Value

Dec 24, 2007

select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
 a            b
----------------------------
tom        small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'.  Please help me find a solution, thanks!

View 4 Replies View Related

SQL Problem With Subquery In Select

Mar 22, 2008

1) Getting this error:
Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near the keyword 'in'.
Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near ','.
Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason...
Here is my procedure. 
 
alter procedure SP_ImportHotels_Step4
As
BEGIN
INSERT INTO classifieds_Photos
( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL )
SELECT classifieds_Ads.Id,
[i.ID] in (Select Min(Images.ID) from Images group by HotelID),
GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads
INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID)
INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID;
END
go
execute SP_ImportHotels_Step4

View 15 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned in second

View 1 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column

note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned because max(id_no) column is absent

if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2

this query return that

insqlhelp ---------------
aaa2

(1 row(s) affected)

this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")

other way is

set to any other text variable to table name,
and concetanate to query,
and execute with exec

but in this way you cant set to my int variable

please help me thanks for all

hra

View 1 Replies View Related

SELECT DISTINCT Subquery

Jul 4, 2006

I the following table:

table1
member_name legacy_id team_name
-----------------------------------------
Bill 1234 nationals
Bill 1234 nationals
Tom 3456 nationals
Tom 3456 orioles

I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.

I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.

My query should return

team_name count
-----------------
nationals 2 - because it occurs for bill, and tom
orioles 1 - because it occurs for tom


If I do something like:

select
distinct(team_name),
count(team_name)
from table1
group by team_name

I get back:

team_name count
-------------------
nationals 3 - because it occurs for bill twice, and tom once
orioles 1 - because it occurs for tom once


I've tried something like:

select
team_name,
count(team_name)
from
table1
where legacy_id in (
select distinct legacy_id from table1
)

I get a syntax error. Regardless, I'm not sure this will give me what I need.

I've tried over a dozen variations of select distinct, joins, etc but with no luck.

Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.

View 4 Replies View Related

Subquery In SELECT Statement Before FROM

Nov 19, 2006

Hello!

I can use querys like these in Access:

SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1

But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".

Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.

-Teemu

View 3 Replies View Related

Calling A Stored Procedure Which Uses A Return @count

Aug 21, 2007

Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:-----------  ALTER PROCEDURE dbo.CountTEstAS   SET NOCOUNT ON    DECLARE @sql nvarchar(100);    DECLARE @recCount int;      DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*)            FROM Pool 'SET @parms = '@recCount1 int OUTPUT'             exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4  My code that calls this stored proc is:         Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True")        connect.Open()        Dim cmd As New SqlCommand("CountTEst", connect)        cmd.CommandType = CommandType.StoredProcedure        Dim MyCount As Int32        MyCount = cmd.ExecuteScalar        connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and  then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks, 

View 5 Replies View Related

Trying To Return Total Record Count With Query

Feb 26, 2007

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>


Thanks for any help!
Dale

View 10 Replies View Related

Insert Query With A Select Subquery

Apr 1, 2008

Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?

View 4 Replies View Related

Subquery Issues In A Select Statement

Nov 28, 2006

Hi there,

I am pulling back records from the DB in this case to get Wheel information. I am pulling back based on user input, but also need to query a second table that contains the Price and model number from another table based on a field being pulled back in the original select.

I am not sure if this makes sense, here is a working copy of the SQL I have , but it's not pretty. There must be another way of stating this statement that i am missing, can anyone give me some suggestiosn?

SELECT tblMacPak2.*,
(SELECT ListPrice
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS ListPrice,
(SELECT PartNumber
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS PartNumber
FROM tblMacPak2
WHERE (Make = N'honda') AND (Model = N'civic') AND (SubModel = N'standard') AND (YearRange = N'2006') AND (Factory_Wheel_Diameter = N'15')

3 selects in one statement...that can't be right.

Thanks,

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved