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.
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.
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
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.
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....
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?
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?
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
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 ')
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
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
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
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
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()
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:
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!
I am having trouble creating a query which will list schools with ALL their Admission = Null. For example, if at least one record is not null, don't list it, but as long as ALL the records have Admission = Null, then list it. I hope that's clear. Thanks!
SchoolTable:
SchoolName | ApplicantName | Admission ------------------------------------------------ North School | Student1 | Admitted North School | Student2 | North School | Student3 | East School | Student4 | East School | Student5 | East School | Student6 | West School | Student7 | Admitted West School | Student8 |
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~
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
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.
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?
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
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
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.
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?
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))
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!
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
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?
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
