Get occurrences of a record

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,706 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,293 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 528 | Replies: 1

•

Join Date: May 2008

Posts: 33

Reputation: 2eXtreme is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

2eXtreme

Offline

Light Poster

Get occurrences of a record

May 28th, 2008

hey guys, i dont suppose you know how i can go from:

id.............result
----------------------
1..............Pass
1..............Pass
1..............Fail
1..............Pass
1..............Fail
2..............Pass
2..............Pass
2..............Fail

to this table:

id...........pass......fail
1............3.........2
2............2.........1

my current query is simply

select id, result from results

I just want to display the total number of occurrences of a result and display them under appropriate headings...I tried performing a union on 2 separate select statements, but it just stacks results on top of one another...

thanks for any help

•

Join Date: Dec 2004

Location: London or Slovakia

Posts: 2,134

Reputation: peter_budo has a spectacular aura about

Rep Power: 10

Solved Threads: 257

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: Get occurrences of a record

May 29th, 2008

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT id, COUNT(NULLIF( result, 'Fail' )) Passes, COUNT(NULLIF( result, 'Pass' )) Fails
FROM results
GROUP BY id
ORDER BY id
SELECT id, COUNT(NULLIF( result, 'Fail' )) Passes, COUNT(NULLIF( result, 'Pass' )) Fails
FROM results
GROUP BY id
ORDER BY id

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
If we helped you to solve your problem, answered your question please mark your post as SOLVED.