|
If table1 has fields Studentname , departmentid
and table2 has fields departmentid,departmentname then can anyone
give me the sql query to get the department name and the number of students in each department.
|
|
|
|
|
Sounds like homework.
Simply join and fetch the data from both tables and then group by department name. Use COUNT to calculate the amount of students.
|
|
|
|
|
Try this
SELECT Studentname , departmentid
FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
T1.departmentid = T2.departmentid
ORDER BY departmentid
Hope it helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Vuyiswa Maseko wrote: Try this
SELECT Studentname , departmentid
FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
T1.departmentid = T2.departmentid
ORDER BY departmentid
The OP wants department name and count of students - your query returns neither.
|
|
|
|
|
Thank u , just what I needed.
select deptname,COUNT(deptid) from emp,department
where emp.deptid =department.id group by deptname
|
|
|
|
|
hi
in my project i want to display a report to employee by month wise achivement.
if user select starting date to ending date means it has to display
month wise . if user select 1st qtr means it has to display like this.
-------------|-------------|-------------|
JAN | FEB | MAR |
-------------|-------------|-------------|
target|achive|target|achive|target|achive|
------|------|------|------|------|------|
5 | 4 | 5 | 5 | 4 | 2 |
output screen is like this.. can u guide me. pls. how to do.
Frontend is asp.net using c# and backend is MYSQL
|
|
|
|
|
If you mean that months are displayed as columns, is it always a quarter or can user select for example 6 months? If the amount of months is dynamic, I think you must rotate them horizontally when displaying the data (asp.net in this case).
|
|
|
|
|
Hello,
I have a problem with the exercise I am currently working with.
The database has 4 table:
PHASE
id Name
1 Morning
2 Afternoon
3 Evening
...
STAFF
id Name
1 John
2 Mary
...
SESSION
id FirstdayOfWeek DayOfWeek Phase
1 5 Jan 2009 Sat 1
2 5 Jan 2009 Sun 3
...
DUTY
Staff Session
1 1
2 2
...
The program is to make report about the number of times that a staff do his/her duty in each phase in a specific month chosen by the user. It may look like this:
REPORT
Staff: John (chosen by user)
Month: Jan 2009 (chosen by user)
Date Morning Afternoon Evening ...
10 Jan 2009 yes no no
11 Jan 2009 no no no
.....
Total 3 6 0
Can you tell me how to make the SQL that give a table like the one above (with Date, Morning, Afternoon, Evening columns) to send it to the Crystal Report? Is this possible in just one SQL query? Thank you in advance.
|
|
|
|
|
I think you should have a look at DATEPART[^] and CASE[^] combined with joins.
|
|
|
|
|
Hi Mika,
thank you for your answer. Of course I have already tried datepart, case and join and it works well with the current example. My problem is the '...' in my writing above. That means the database can be change and unfortunately, my report depends on the Phase table. The number of column, the name of each column, all are dynamic. Can you tell me how to do this in just one SQL query? If it is impossible, is there any solution like adding some more tables or writing some more code?
|
|
|
|
|
Have you tested PIVOT. If you already have and still not getting the desired result, I think you should use dynamic SQL either by creating a procedure or from the client. If I understood you correctly, first fetch all the phases, after that construct a SQL query with amount of columns based on phase table. After that, use that statement to return the data.
|
|
|
|
|
I have put TRY CATCH in my Stored Procedure but it fails when any invalid table or invalid column error occures and it prompts error msg directly in place of putting it in TRY CATCH Error Log.
Is there any way to catch such type of errors through TRY CATCH.
Please Let me know.....
Thanks in advance
With Thanks & Regards
Amit Sk Sharma
|
|
|
|
|
Unless you are using dynamic sql you will get compile time error and that's not possible to handle with try...catch. So by using dynamic sql you can catch the error at execution time.
|
|
|
|
|
i used "create database for attach" to attach an old database to the sqlserver but the database files (like *.mdf and *.ldf and etc) have to be in the previous folder (the old folder that i used to use it as my database folder)
how can i restore data without having to copy it in the old folder ?
i do not want to use backup and restore because they need to have a device and i do not know what can i do if the device deleted . do you know ?
|
|
|
|
|
reza assar wrote: to attach an old database to the sqlserver but the database files (like *.mdf and *.ldf and etc) have to be in the previous folder
They don't have to be in the original folder, but if you're moving the log file to a new location, I think you must use ATTACH_REBUILD_LOG.
From CREATE DATABASE[^]: "FOR ATTACH: Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached"
See also: How to: Move a Database Using Detach and Attach[^]
|
|
|
|
|
reza assar wrote: i do not want to use backup and restore because they need to have a device and i do not know what can i do if the device deleted . do you know ?
You don't need a device, you can backup and restore to a file. The UI is a bit confusing, as it's mainly intended for backup to an external device, but you simply change some radio buttons to get to the right place.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
hi
i have a problem with a new component installed on my vs 2005
the component`s name is "devExpress"
i used it in one of my applications . but my application will crash when i start it on the other computers (the computers that does not have these components) how can i solve this problem ?
thanks in advance !
|
|
|
|
|
|
Hi
I need Script to backup selected database of SQL Server 2000. Script should have facility to add the database in the backup schedule in future.
Can any one help me out.
Regards
Vivek
|
|
|
|
|
To get you to the start, open Management Studio, create the backup and script it to a SQL Script.
You can also schedule the backup and then script the job which is created under the Sql Server Agent.
|
|
|
|
|
What is the datatype of the audio or video data to store in sql2005 database. 
|
|
|
|
|
|
i m new candidate to learning vb.net. my que is database connectivity in vb .net using Access..?
|
|
|
|
|
Try connectionstrings.com
Search through the articles here and on Google, there are plenty of excellent samples around. Learn to do some research before dropping such an open question into a forum. When you run into a specific problem we will be more than happy to help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
use oledb.
But i suspect that you did not buy a Book 
try and get a Book that will guide you step by Step.
http://www.apress.com/book/view/143021614x[^]
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
