Select And Count From Related MS SQL DB

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

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 View Related

SQL Count And Related Questions

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

Count Of Related Records??

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

Analysis :: DAX For Distinct Count Of Related Column Without Returning Out Of Memory Error?

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

Select 2 Related Tables

May 25, 2006

hi this is probably a dumb question with an easy answer but does anyone know how to do this...

2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)

basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?

do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?

View 10 Replies View Related

Select All From Two Many-to-many Related Tables

Oct 11, 2007

Hello, everyone!

Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables.
Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?

I would greatly appreciate any answers

View 9 Replies View Related

T-SQL (SS2K8) :: Select Minimum Date From Related Table

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

Select Statement That Will Output Related Rows With Different Column Data Per Row?

Apr 27, 2008

Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:

rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...

where for each ID, I have 3 rows that are associated with it and with different corresponding values.

I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.

i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..

After my loop, I will just do a select * from #someTempTable

Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005

View 2 Replies View Related

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

Jun 21, 2006

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 9 Replies View Related

Select Statement With Count Within Another Select

Aug 23, 2013

I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).

Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.

SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2

[code]....

View 1 Replies View Related

Select Count

Oct 25, 2007

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 1 Replies View Related

Select Count

Nov 18, 2007

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 8 Replies View Related

Select Count(*)

Mar 14, 2008

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 7 Replies View Related

Count() And Sub-select

Aug 26, 2004

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 3 Replies View Related

Please Help On Select COUNT

May 3, 2007

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 1 Replies View Related

SELECT COUNT...

Jan 9, 2008

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 16 Replies View Related

Select Count Help

Feb 4, 2008

View 5 Replies View Related

Select Rows Where Row Count &> 3

Jan 4, 2007

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 5 Replies View Related

Select Count Issue

Oct 18, 2007

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 3 Replies View Related

Trying To Get A Count In A Select Statement

Oct 21, 2007

When I try and execute this query I get the belwo error.
I want to get the ItemName and the Count as one column.
How can this be done?
SELECT itemName, itemName +' - '+ COUNT(itemName) AS itemNameCount FROM tblItems GROUP BY itemName
ERROR: Conversion failed when converting the nvarchar value 'Spark Plug - ' to data type int.

View 3 Replies View Related

Select Count(*) For Getting # Of Records

Nov 23, 2007

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 1 Replies View Related

COUNT ( FROM Nested SELECT)

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

SELECT COUNT Of MAX (GROUP BY)

Jul 16, 2006

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 4 Replies View Related

SELECT COUNT Of DISTINCT

Jul 19, 2006

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 3 Replies View Related

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

Help With SELECT Query With COUNT

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

How To Get Count Of Another Table In A Select

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

Using A Count W/in A Sub Select Statement

Mar 2, 2006

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 1 Replies View Related

Get Count From Union Select

Jul 20, 2005

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 1 Replies View Related

Help With COUNT In SELECT Statement

Jul 20, 2005

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 2 Replies View Related

Problem On Select Count(*)

Sep 4, 2007

HI,

We have one table have problem when run "select count(*) from table". This is a sql 2000 server. it returned different count everytime when I ran the "select count(*) from table". I used DBCC to check the table and did not find any problems. I can fix it by recreate the table and reimport the data, but the problem come back after we update some data in the table monthly with a stored procedure. Any suggestion?

Thanks

Sand

View 1 Replies View Related

The Awful SELECT COUNT(*)

Apr 25, 2008

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 1 Replies View Related