MS SQL Server :: Count(*) And Select In The Same WITH Statement

Help With COUNT In SELECT Statement
Could someone assist with getting the count function working correctlyin this example please. I know the count function will return all rowsthat do not have null values, but in this case I want to count all therows except those with a zero sale price, (which are unsold).The table shows works offered for sale by an artist, with a positivefigure under SalePrice indicating a sale, and I want to count thenumber sold by each auction house, and sum the sale price by auctionhouse. The table is as follows:NameSalePriceAuctionDowling12000ChristiesDowling 0ChristiesDowling10000ChristiesDowling 0ChristiesDowling 0ChristiesDowling 6000SothebysDowling 0SothebysDowling 0SothebysDowling 8000SothebysDowling 0SothebysDowling 0SothebysDowling 0SothebysWhen I run this query:SELECT MyTable.Name, Count(MyTable.Name) AS [Number],Sum(MyTable.SalePrice) AS TotalSales, MyTable.AuctionFROM MyTableGROUP BY MyTable.Name, MyTable.AuctionHAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));The results are:NameNumberTotalSalesAuctionDowling 5 22000 ChristiesDowling 7 14000 SothebysThe TotalSales is correct, but the Number (Count) is incorrect, as therows with zero were also included. The results should be:NameNumberTotalSalesAuctionDowling 2 22000 ChristiesDowling 2 14000 SothebysHow do I prevent the unsolds (zeros) being counted?Thanks in advance,John Furphy

View Replies !

Using A Count W/in A Sub Select Statement
I'm trying to create a DTS package that uses CDO to send users an email. I need to create a sql query that counts two columns. I also need to create aliases for these two columns and then reference this in the sendEmail function. I have something that looks like this but I'm getting a DTS error. I think that it's because I'm not using an alias to reference Valid and Invalid. Can someone tell me how to alias the subselect columns correctly?? thanks :)

select advertiseremail, accountnumber from miamiherald where AdvertiserEmail is not null (select Valid = (select count (*) from miamiherald where validad = 1), Invalid = (select count (*) as Invalid from miamiherald where validad = 0))

View Replies !

Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement
I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN
SELECT Code, COUNT(Code)as exprCount1a
FROM dbo.[Test]
WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code
END
The results of the statement:
Code | exprCount1a
1              22              44              1
I would like the following results:
Code | exprCount1a
1              22              4
3              04              1
Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results.
Thank you in advance

View Replies !

Count # Of Results From SELECT Statement
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View Replies !

TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,

View Replies !

Zero Count Values Not Appearing In SELECT Statement
Hi all

I have the following tables:

Code Snippet
CREATE TABLE #Lkp_Circle
(
ID INT ,
Abbreviation varchar(50)
)
GO
CREATE TABLE #Lkp_OtherCircles
(
Circle varchar(50)
)
GO
CREATE TABLE #Tbl_User
(
ID INT,
Name VARCHAR(50),
IsActive bit
)
GO
CREATE TABLE #Tbl_UserDetails
(
AssociateID INT,
CircleID INT
)
GO
INSERT INTO #Lkp_Circle VALUES (1,'C1')
INSERT INTO #Lkp_Circle VALUES (2,'C2')
INSERT INTO #Lkp_Circle VALUES (3,'C3')
INSERT INTO #Lkp_Circle VALUES (4,'C4')
INSERT INTO #Lkp_Circle VALUES (5,'C5')
INSERT INTO #Lkp_Circle VALUES (6,'C6')
INSERT INTO #Lkp_Circle VALUES (7,'C7')
GO
INSERT INTO #Lkp_OtherCircles VALUES ('C3')
INSERT INTO #Lkp_OtherCircles VALUES ('C4')
INSERT INTO #Lkp_OtherCircles VALUES ('C5')
INSERT INTO #Lkp_OtherCircles VALUES ('C6')
GO
INSERT INTO #Tbl_User VALUES ( 101,'U 1','True')
INSERT INTO #Tbl_User VALUES ( 102,'U 2','True')
INSERT INTO #Tbl_User VALUES ( 103,'U 3','True')
INSERT INTO #Tbl_User VALUES ( 104,'U 4','True')
INSERT INTO #Tbl_User VALUES ( 105,'U 5','True')
GO
INSERT INTO #Tbl_UserDetails VALUES(101,3)
INSERT INTO #Tbl_UserDetails VALUES(102,4)
INSERT INTO #Tbl_UserDetails VALUES(103,5)
INSERT INTO #Tbl_UserDetails VALUES(104,5)
INSERT INTO #Tbl_UserDetails VALUES(105,3)
GO

SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total]
FROM
(
SELECT DISTINCT 'Circle' = CASE
WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation
WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others'
ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount'
FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN
#Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID
WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0
) AS PivotTable
GROUP BY Circle
WITH Cube

DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles

----EXPECTED RESULT
--Circle HeadCount
--C3           2
--C4           1
--C5           2
--C6           0
--Others     0
--Total       5
--
----ACTUAL RESULT
--Circle HeadCount
--C3          2
--C4         1
--C5          2
--Total      5

The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?

View Replies !

Cannot Construct A SELECT DISTINT COUNT... Statement In SSCE 3.1
Hi everyone,

sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.

Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:

SELECT DeptID, DeptName,

(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND
(Documents.Active = 'True') AS CountOfActive
FROM Dept

Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.

Any thoughts on how I may solve this?

Much thanx!
Shalan

View Replies !

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
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

View Replies !

Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization....

I have a query that looks like this:

SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)

it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View Replies !

Properties Row Count Not = Select Count(*)
I have a SQL2000 table, and when I display Properties, it says the row count = 927, but when I do select count(*), I get 924. I did a refresh on everything (since refresh is often needed), finally exited SQL Ent Mgr, went back in with the same result. I believe 924 is the correct count ..... Is that table corrupted somehow ? Can I trust the count in "Properties" for other tables ?

View Replies !

Combining 2 Select With Count And Datediff Into 1 Select. Need Help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete          Jobs completed within 5 days

10                                 5

-------------------------------------------------------------------------------------------------

SELECT     COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM         dbo.Project
WHERE     (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
                      (DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

---------------------------------------------------------------------------------------

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

View Replies !

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View Replies !

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:

Field01           Field02

-------------          ----------------------

192473           8461760

192474           22810

Because other reasons. I need to modify that query to:

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810

Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

SQL COUNT Statement
I am trying to count records in SQL Server. I have this Stored procedure but I am getting SQL errors.

Alter Procedure usp_rptQualityReport3 As

SELECT * FROM viewQualityReport

SELECT COUNT([FailureReason]) AS FC

WHERE
(((viewQualityReport.FailureReason) <> N'NONE'))

ORDER BY
FC

I am trying to count records that have like FailureReasons. I am selecting all the records from the view I created and then trying to count the records in the second Select statement. Basically what I want to do is counf them so I can then rank them starting with failure reasons that happen the most. I don't know what I am doing wrong.

View Replies !

Please Help With The SQL COUNT Statement!
I'm trying to run this SQL statement in my ASP code, sql="SELECT COUNT(*) FROM order_list WHERE ref_index='"&ref_index&"'"

The problem is, should i create a variable to store the returned integer? I tried rs_itemcount = conn.execute(sql) and then response.write(rs_itemcount.value)

But it returns with an error... any ideas how to store the integer and use it?!

thx!

View Replies !

Where Statement Again A Count
I can insert into a temp table and get this to work fine but it am unable to pass my sdate and endate parameter from reporting services.

This Works Fine I Just want to throw in a where statement. How do you get this to work? I tried [] () ''

WHERE Cola=Count(*) = Colb=Count(DidNotAttend)

Use Consumer

Select TripsId.ID#, Cola=Count(*), Colb=Count(DidNotAttend)

FROM Trips INNER JOIN

TripsId ON Trips.Rec# = TripsId.TripId#

Group by TripsId.ID#

View Replies !

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

Using A Count If Within A Group By SQL Statement?
I have the following SQL Statement:
SELECT CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL

View Replies !

Increment A Count I Sql Statement
How can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get

miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3

View Replies !

Trying To Count A Case Statement?
I need to get a total count of leads and then separate the counts by either Retail or Wholesale -

Here's my table schema -

CREATE TABLE [dbo].[Sent] (
[IdentID] [int] IDENTITY (1, 1) NOT NULL ,
[LeadID] [bigint] NOT NULL ,
[AffiliateID] [bigint] NULL ,
[PartnerID] [int] NULL ,
[FranchiseID] [bigint] NULL ,
[FirstName] [t_Name] NULL ,
[LastName] [t_LastName] NULL ,
[Address] [t_Address] NULL ,
[Zip] [t_ZipCode] NULL ,
[Make] [t_Make] NULL ,
[Model] [t_Model] NULL ,
[DateIn] [datetime] NULL ,

Here's my query - Since I'm grouping by the partnerid

select distinct make, count(leadid) as TotalCount,     case
            when PartnerID = 1 then 'retail'
            else 'wholesale' end
            as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make

Here's a sample my current output -

Acura
1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale

Here's the output I need

Make
Total Count
RetailCount
WSCount

Acura
798
510
288

Audi
256
75
181

View Replies !

SQL Statement Count NULL Values
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

SProc - Ad Hoc Sql Statement With COUNT For Exec(@SQL) ??
Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

View Replies !

Help With Query (count With Case Statement)
Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")

any help much appreciated..

thanks!
mike123

View Replies !

Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View Replies !

Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt

View Replies !

Count The Number Of Rows In A UNION ALL Statement
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark

View Replies !

SQL Statement, Adding Two COUNT/CASE Statements

SELECT     COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
                      COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM         content_hits_tbl
WHERE     (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())

=======================

How do I add up both COUNT/CASE columns? Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total

I tried this and it doesn't work. I get invalid column names error for both.

I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total

You would think that there would be some gui functionality in VS08 that would do this...

Thoughts are greatly appreciated!

TT

View Replies !

Select Count(*)
I have sql statement like "select count(*) from table where id = 1", and I want to assign the result to label.text. How do I do that? Thanks.

View Replies !

Select Count
I need to select total number of rows from my data base table....here is what ive been trying....I know it wrong...but maybe someone can fix it. Thank you very much.
Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As BooleanDim MyConn As New Data.SqlClient.SqlConnection(ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommand("Select count * from ClassifiedAds", MyConn)Dim dr As Data.SqlClient.SqlDataReader
'cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@UserName", textbox_username.Text))
' cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@Password", textbox_password.Text))
cmd.Connection.Open()
dr = cmd.ExecuteReader()
dr.Read()
If dr.HasRows Then
Label_totalclassifieds.Text = dr.Read
Return True
Else
dr.Close()
cmd.Connection.Close()
Return False
End If
End Function

View Replies !

Select Count
Hello, I would like to count the number of items in a table. I used the following code:1 Dim Comments As Integer
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
3 Dim SqlCommand As New SqlCommand("SELECT COUNT(CommentID) FROM Comments WHERE ThemeID = '3', cnn")
4
5 cnn.Open()
6 Comments = SqlCommand.ExecuteScalar()
7 cnn.Close()
But this only gives me the error message "ExecuteScalar: Connection property has not been initialized." Can anyone help me with this? Thanks

View Replies !

Count() And Sub-select
Hello,

I'm new to SQL.
For a statistic application, I wish know the subtotal of lines pro region (Mitte, ost, west, ost, etc).

How can I do that?

A lot of thx for your help and time,
Regards, Dominique

Code:

SELECT
distinct case
when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = 'AG' then 'mitte'
when ANZSUCHEN.KANTON = 'AI' then 'ost'
when ANZSUCHEN.KANTON = 'AR' then 'ost'
when ANZSUCHEN.KANTON = 'BE' then 'bern'
when ANZSUCHEN.KANTON = 'BL' then 'mitte'
when ANZSUCHEN.KANTON = 'BS' then 'mitte'
when ANZSUCHEN.KANTON = 'FR' then 'west'
when ANZSUCHEN.KANTON = 'GE' then 'west'
when ANZSUCHEN.KANTON = 'GL' then 'ost'
when ANZSUCHEN.KANTON = 'GR' then 'ost'
when ANZSUCHEN.KANTON = 'JU' then 'west'
when ANZSUCHEN.KANTON = 'LU' then 'mitte'
when ANZSUCHEN.KANTON = 'NE' then 'west'
when ANZSUCHEN.KANTON = 'NW' then 'mitte'
when ANZSUCHEN.KANTON = 'OW' then 'mitte'
when ANZSUCHEN.KANTON = 'SG' then 'ost'
when ANZSUCHEN.KANTON = 'SH' then 'ost'
when ANZSUCHEN.KANTON = 'SO' then 'mitte'
when ANZSUCHEN.KANTON = 'SZ' then 'mitte'
when ANZSUCHEN.KANTON = 'TG' then 'ost'
when ANZSUCHEN.KANTON = 'TI' then 'west'
when ANZSUCHEN.KANTON = 'UR' then 'mitte'
when ANZSUCHEN.KANTON = 'VD' then 'west'
when ANZSUCHEN.KANTON = 'VS' then 'west'
when ANZSUCHEN.KANTON = 'ZG' then 'mitte'
when ANZSUCHEN.KANTON = 'ZH' then 'ost'
end as region,
(SELECT count(*) FROM ANZSUCHEN WHERE ANZSUCHEN.KANTON = 'FR')
FROM
ANZSUCHEN
GROUP BY
ANZSUCHEN.KANTON
ORDER BY
region

Results:

Code:

region (No colomn name)
bern34
mitte34
nicht_zugeteilt34
ost34
west34

View Replies !

SELECT COUNT...
Hallo!
I have a table student(teacherID, studentID, studentName...) that contains data for a certain student and a table teacher(teacherID, teacherName...) that contains data for the teacher. Student can be imagine as follow:

tID | sID ... other fields
----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
3 8

For example, teacher of tID=1 teachs to 4 students.
I'd like to select all the fields from teacher where the teacher has more than x students.
Is it possible? How can I do?
Thank you!

View Replies !

Please Help On Select COUNT
I have the following code in VB.Net trying to count # of records in the datatable. Upon execution of the code, it returns only 1 record. I know for fact that there are more than 1 record in the datatable (there are 230 records in the table).

Can somone please tell me where is my problem ?
Public Function Get_Record_Count(ByVal Table_Name As String, ByVal Criteria As String) As Long
Dim tbl As DataTable

strSQL = "SELECT count(*) FROM [" & Table_Name & "]"
If Len(Criteria) > 0 Then
strSQL &= " WHERE " & Criteria
End If

ADO_Adapter = New OleDb.OleDbDataAdapter()
tbl = New DataTable(Table_Name)
ADO_Adapter.SelectCommand = New OleDbCommand(strSQL, ADO_Conn)

Dim Builder As OleDbCommandBuilder = New OleDbCommandBuilder(ADO_Adapter)

ADO_Adapter.Fill(tbl)
'Return # of records found in table
Get_Record_Count = tbl.Rows.Count.ToString
tbl.Dispose()

End Function

View Replies !

COUNT ( FROM Nested SELECT)
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 Replies !

Select Count(*) For Getting # Of Records
Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()

I am getting an error that that's no data in the table.
Any suggestions?

View Replies !

Select Count Issue
Hello,We have a database filled with local restaurants for a directory. What I'm trying to do is display a list of all the cuisines and the number of restaurants who serve that type of cuisine in parentheses. It should look like the example below:American (12)Chinese (3)Italian (5)...Here are the tables with only the relevant columns: restaurants---------------...cuisine_id... cuisines-----------cuisine_idcuisine_name... So basically I want to pull all the names from the cuisines table and then the restaurant count from the restaurants table. The only way I can think of doing that is to grab the cuisine_ids, put them in an array and then use that in a loop to get the count from the restaurants table. That's not very elegant. Is there a better way?Thank you

View Replies !

Select Rows Where Row Count &> 3
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid.
I am not quite sure how to do this.
Any ideas?
~mike~

View Replies !

Select And Count From Related MS SQL DB
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 Replies !

Get Count From Union Select
Hi,I'm trying to get the count of rows from the union of several tables.My code is:select count(*) from (select * from #AdvSearch_Mainunionselect * from #AdvSearch_Atty)This will not get past the syntax check saying that the error occurs onthe final closing ")".Can someone tell me how to correctly write this?Thanks,Glen--------------------------Numbers 6:24-26----------------------------------------------------Numbers 6:24-26--------------------------*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

SELECT COUNT Of MAX (GROUP BY)
Hello

I want to get the COUNT of

SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View Replies !

SELECT COUNT Of DISTINCT
helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you

View Replies !

The Awful SELECT COUNT(*)
I found some really helpful code that allows for quick retrieval of select count(*). But am I right in saying that select count(*) can cause locks on the table?

This is the code
dbcc updateusage (N'Test')
go
select count(1) from Testdb
go
CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (

        @sTableName sysname -- Table to retrieve Row Count
        )

    RETURNS INT -- Row count of the table, NULL if not found.

/*
* Returns the row count for a table by examining sysindexes.
* This function must be run in the same database as the table.
*
* Common Usage:
SELECT dbo.udf_Tbl_RowCOUNT ('')

* Test
PRINT 'Test 1 Bad table ' + CASE WHEN SELECT
       dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
        THEN 'Worked' ELSE 'Error' END

* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
***************************************************************/

AS BEGIN

    DECLARE @nRowCount INT -- the rows
    DECLARE @nObjectID int -- Object ID

    SET @nObjectID = OBJECT_ID(@sTableName)

    -- Object might not be found
    IF @nObjectID is null RETURN NULL

    SELECT TOP 1 @nRowCount = rows
        FROM sysindexes
        WHERE id = @nObjectID AND indid < 2

    RETURN @nRowCount
END
GO

GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC
GO

use Test -- assuming the UDF was created in Test
go

SELECT [name]
     , dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
    FROM sysobjects
    WHERE type='U' and name != 'dtproperties'
    ORDER BY [name]
GO

View Replies !

Help With SELECT Query With COUNT
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 Replies !