|
Hello,
I have a child table that contains rows related to a parent table. I would like, in a single SELECT query for the parent table, to have two counts, one for rows in the child table that match a certain criteria, and another that match some other criteria.
A simple example, a table of Classrooms, with a child table Students, where each student is either male of female; and the query should return ClassroomID, NumMales, and NumFemales.
I can easily get only one count (males for example) using GROUP BY with something like:
SELECT COUNT(StudentID) AS NumMales FROM Students
GROUP BY Sex WHERE Sex = 0but I can't find a way to get two different aggregate functions to work on the same table.
In my case it's actually a bit more complicated, because I have three tables related. But a little pointer on how to do it with two tables should help me get started.
Any ideas? Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Like this:
SELECT COUNT(StudentID) AS Females,<br />
(SELECT COUNT(StudentID) AS Males<br />
FROM dbo.Students<br />
WHERE (Sex = 'M')) AS Males<br />
FROM dbo.Students AS Students_1<br />
WHERE (Sex = 'F')
|
|
|
|
|
|
Is there anything like C# style ternary operator in SQL?
|
|
|
|
|
|
Very many thanks for your great answer, Colin
|
|
|
|
|
I created a dts package that imports data into sqlserver. I run the dts package from a vb.net application on a machine that does not have sqlserver installed. I get the error quoted above. I used an installer project to install my vb.net application on the machine running the application.
Included in the folder with my exe is dtspkg.dll, Interop.DTS.dll, as well as some other config and dll files. Why am I getting this error?
|
|
|
|
|
It sounds like you need to redistribute the DTS files.
The interop library is just a component which uses these files.
Have a look at this[^].
|
|
|
|
|
Exactly what I needed. Thanks 
|
|
|
|
|
I am using Access to work in a large SQL database. There are stored procedures in the db. When I run them from Access, I get a message saying the procedure has executed and has not returned results (which is correct). Problem is that examining the table it is apparent only a small portion of the rows were updated.
If I run the procedure from osql, everything is fine. There are no parameters, it is simply copying a very large amount of information. I suspect Access is timing out.
Is there any way to see exactly what Access is doing when I run the procedure?
|
|
|
|
|
When i run my crystal report from my installed application it prompts me for database logon information. Does anyone know how to get rid of this so it just uses the connection string
everything else is using??????
|
|
|
|
|
Heres the crack:
During data binding on a repeater control, the RepeaterItem's DataItem property is populated with the current DataRowView that it has enumerated over whilst and during the invokation of the OnItemDataBound routine and ItemDataBound event.
This provides very handy access to the data that this UI component is building its visual dependancy on.
HOWEVER, the moment that this RepeaterItem has finished its data binding and is added to the repeater controls Items collection, its DataItem property is de-referenced and you can no longer gain access to the underlying data.
Now I understand that this may be to encourage good practice in the reliance upon using ViewState, but I just don't have the time to fanny about with all these loose type castings when all I want to do is cast the DataItem{ DataRowView }.Row to my strong typed row from my dataset so I can immediately work with the Repeater controls Items collection to determine the state of controls and work directly with their associated data!
WHY is the DataItem property de-referenced?
Still, a workaround for me at the moment is the handy correlation between the DefaultView's row indexing and the RepeaterItem's ItemIndex property, ALA:
(ItemDataBound Event Context):
ctype(ctype(e.Item.bindingcontainer, DataList).datasource, MyDataTable).Item(e.Item.itemindex) is ctype(e.Item.DataItem, DataRowView).Row
returns True.
Why does it have to be like this!??! Why not just keep the reference in DataItem of the RepeaterItem class? DataList is also the same. Have I missed something totally blatant here?
I look forward to your views on the subject!
|
|
|
|
|
Hi,
I have, for example, a Stored Procedure wich validates users into my web.
I'd like to validate the users's input in order to enhance security.
What would be the best way to validate the input inside the Stored Procedure?
for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--"
thanks,
H
|
|
|
|
|
hhrafn wrote:
for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--"
Well, if you are using stored procedure this shouldn't be too much of a problem. You are passing the parameters to the stored procedures correctly, aren't you? I mean your code is something like this:
SqlCommand cmd = new SqlCommand("MyStoredProcName");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userNameParam", "joe");
If you add parameters in this way you are protected from a first order attack. If your command string is something like
EXEC MyStoredProcName @userNameParam = 'joe' then you should replace it with code like the first example above.
In your stored procedure you can then have code like this
IF EXISTS (SELECT * FROM UsersTable WHERE UserName = @userNameParam)
BEGIN
-- Do stuff based on the user existing
END
ELSE
BEGIN
-- Do stuff based on this being an incorrect user
END
For more information see SQL Injection Attack and Tips on How to Prevent Them[^]
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
http://support.microsoft.com/default.aspx?scid=kb;en-us;814414
Error: Attempting to connect to server: <servername>; Unable to connect to the registry on the server (<servername>), or you are not a member of the OLAP Administrators group on this server.
|
|
|
|
|
|
i used an ASP.NET Web Application as front end and used Oracle 8i as the backend..on Windows 2000 Advanced Server as OS...
the connectivity between the two was working properly.. but then.. i had to use another computer which had oracle 9i.. i created the same database in Oracle 9i.. but on debugging it showed error on the line
me.oledbconnection1.open()
and mess was that the networking components are not installed..
wat to do now
|
|
|
|
|
vishalmishra wrote:
the networking components are not installed..
wat to do now
If you haven't installed the networking components then you should do that. If you have installed them then maybe the installation is damaged and you should try and install them again.
DISCLAIMER: I know nothing about Oracle, it just seems to me that the error message is fairly self explanitory.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Actually wat networking components i need to install ..??? i could not get it...
the real errror was
Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed
and i went for Full Oracle 9i installation..
|
|
|
|
|
Hi,
I'm currently working on some SQL stuff as part of a project I've been assigned to, and I have a newbie question regarding the GROUP BY clause in a SELECT statement.
I have a table, Activity_Session_Summary, that contains information about the results of the online quizzes that our product administers. Each tuple represents a summary of a user's results for a specific attempt at a specific quiz. Its columns include 'ActivityID', 'PercentCorrect' and 'WhenCompleted'.
What I need is a SELECT statement that returns the PercentCorrect value for the most receently completed quiz attempt, for each activity. So far I have
SELECT ActivityID, MAX(WhenCompleted) FROM Activity_Session_Summary
WHERE StudentID = '%s' AND ActivityID IN ( %s )
GROUP BY ActivityID
where the %s's are format placeholders.
This statement almost does what I need, but obviously doesn't give me the PercentCorrect. However I can't just add PercentCorrect to the SELECT's column list, because SQL Server politely tells me "'PercentCorrect' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'
Can anyone point out what I need to do to get this to work as required?
|
|
|
|
|
Okay - Before I answer your question I am going to do my usual pointer to this article on SQL Injection Attacks and How to Prevent Them[^] whenever I see code that could potentially be attacked.
On to your question. What you need to do is take the statement you have already and use that as a subquery and have the table join onto itself via the subquery. It sounds more difficult than it is.
SELECT main.ActivityID, main.PercentComplete, main.WhenComplete
FROM Activity_Session_Summary AS main
INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenComplete
FROM Activity_Session_Summary
WHERE StudentID = %s AND ActivityID IN ( %s )
GROUP BY ActivityID) AS sub
ON main.ActivityID = sub.ActivityID AND main.WhenComplete = sub.WhenComplete
WHERE StudentID = '%s' AND ActivityID IN ( %s )
I've left your format placeholders even though they represent a potential security risk.
Your original query has now been made the subquery and as you can see, nothing has changed there except that I named the aggregate column so I could refer to it later.
The main query selects the data that you want and joins onto the subquery. You will notice from the join that it is joined on the result of the aggregate as well as the ActivityID (I'm guessing that is a foreign key onto another table). Finally, your where clause is repeated on the main query, although it may not be needed - I don't know enough about your data model to make that determination. If it isn't needed it won't do any harm.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi Colin,
Wow! Thanks for such a rapid and comprehensive reply, I really appreciate it.
I also appreciate your warning re: injection attacks. However, this project is for internal use with hardcoded input values, and will not be accessible in any way outside the machine it's run on. Famous last words? Well, if our security is so screwed that someone can access the script and modify the SQL statement then I can think of many easier ways for them to accomplish their evil ends. But I appreciate your concern!
I think I understand the SQL you gave. I was hoping that there was a way to do this without subqueries (they make my head hurt a little at times), but suspected that they would be necessary.
I think that the StudentID /is/ necessary in the main query. It's theoretically possible that two rows in the result set don't differ in ActivityID or WhenCompleted, but only by StudentID. Not likely, as it would involve two students completing the same activity within a second of each other (or whatever the resolution of DateTime is in SQL Server), but possible none the less.
Again, thanks a lot Colin, you've really helped me out here, and hopefully helped me learn a little so I don't ask the same question in a different form in 2 months time!
Cheers,
Pete
|
|
|
|
|
moredip wrote:
I think that the StudentID /is/ necessary in the main query. It's theoretically possible that two rows in the result set don't differ in ActivityID or WhenCompleted, but only by StudentID. Not likely, as it would involve two students completing the same activity within a second of each other (or whatever the resolution of DateTime is in SQL Server), but possible none the less.
Best to leave it in then. If it is taken out then it can be a nasty time-consuming bug to track down if that condition does happen. I know, I spent two days tracking something similar, which was why I was cautious about removing it without knowing more about your data.
moredip wrote:
thanks a lot Colin, you've really helped me out
Your most welcome.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi again Colin,
I modified the SQL you provided slightly from this:
Colin Angus Mackay wrote:
SELECT main.ActivityID, main.PercentComplete, main.WhenComplete
FROM Activity_Session_Summary AS main
INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenComplete
FROM Activity_Session_Summary
WHERE StudentID = %s AND ActivityID IN ( %s )
GROUP BY ActivityID) AS sub
ON main.ActivityID = sub.ActivityID AND main.WhenComplete = sub.WhenComplete
WHERE StudentID = '%s' AND ActivityID IN ( %s )
to this:
SELECT main.ActivityID, main.PercentCorrect
FROM Activity_Session_Summary AS main
INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenCompleted
FROM Activity_Session_Summary
WHERE StudentID = '%s' AND ActivityID IN ( %s )
GROUP BY ActivityID) AS sub
ON main.ActivityID = sub.ActivityID AND main.WhenCompleted = sub.WhenCompleted
WHERE main.StudentID = '%s'
I just wanted to confirm with you that removing the final AND ActivityID IN ( %s ) makes sense. It seems to me that it's redundant, since the main.ActivityID = sub.ActivityID join will ensure that only ActivityID's in the supplied ( %s ) list will be considered. Is that correct?
Cheers,
Pete
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
