database query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ocptime
Forum Newbie
Posts: 11
Joined: Thu Mar 18, 2004 4:59 am
Location: Trivandrum, Kerala, India

database query

Post by ocptime »

Db is liket his

my table is this

id|testId|studentId|marks|startTime|endTime |status |result
-------------------------------------------------------------
1 1 96 5 1079600091 1079600108 Completed Passed
2 2 96 6 1079600158 1079600168 Completed Passed
3 1 96 1 1079600413 1079600421 Completed Failed



Pls tell me how to query that gets as result the totel no of testtaken Count(TestId)
and No of passes and no of failed ie FailCount and PassCount in 1 query can i use Group by
Plshelp

Thanx in Advance
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Play around with it...

Code: Select all

select 
 count(testId), testId, result
from
 table
group by
 result, testId
ocptime
Forum Newbie
Posts: 11
Joined: Thu Mar 18, 2004 4:59 am
Location: Trivandrum, Kerala, India

Post by ocptime »

thanx for the Help but my idea is to display like this
my output should be like this

Test TestCount FailCount PassCount
PHP 5 0 3
IQ 10 2 8

Just an ex:

Thanx
ocptime
Forum Newbie
Posts: 11
Joined: Thu Mar 18, 2004 4:59 am
Location: Trivandrum, Kerala, India

Post by ocptime »

Hi JAM i changed the query to

$sql="SELECT count(r.testId), r.testId, t.title, r.result FROM qb_test_report AS r, qb_test AS t
WHERE r.status = 'Completed' AND t.testId = r.testId
GROUP BY result, testId" ;$sql3="SELECT count(r.testId), r.testId, t.title, r.result FROM qb_test_report AS r, qb_test AS t
WHERE r.status = 'Completed' AND t.testId = r.testId
GROUP BY result, testId" ;

but i need the above result.my result now is this:

pls take a look


count( r.testId ) testId title result
5 1 Test Your IQ? Failed
2 1 Test Your IQ? Passed
1 2 PHP Passed
ocptime
Forum Newbie
Posts: 11
Joined: Thu Mar 18, 2004 4:59 am
Location: Trivandrum, Kerala, India

Post by ocptime »

ok ihav to take this sql query result and after that using mysql fetch row i will check using PHp and then produce the desired result display

will send u the details
Bye

Thanx again for uer Help
Post Reply