Solved

Query to get total counts of multiple bit fields in each row

Posted on 2006-11-09
5
240 Views
Last Modified: 2012-06-27
I  have a table with the following fields

FK_ID - foriegn key ID
PK_ID - unique PK
REVIEWED - bit
REJECTED - bit

I would like a query that, for a particular FK_ID value, returns a count of:

a. total number of rows matching the FK_ID
b. total number of rows matching the FK_ID that have REVIEWED=1
c. total number rows matching the FK_ID that have REJECTED=1

I'm sure the answer is already out there but a search didn't find this specific case.

Thanks,

Tim
0
Comment
Question by:tfcallahan
  • 3
  • 2
5 Comments
 

Author Comment

by:tfcallahan
Comment Utility
p.s. The results should look something like:

FK_ID   Total    Rejected   Reviewed
1234    6          1             5
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
SELECT 'Total', COUNT(*) FROM table WHERE FK_ID=x
UNION ALL
SELECT 'REVIEWED',COUNT(*) FROM table WHERE FK_ID=x AND REVIEWED=1
UNION ALL
SELECT 'REJECTED',COUNT(*) FROM table WHERE FK_ID=x AND REJECTED=1

0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
Comment Utility
SElECT x as FK_ID,
(SELECT COUNT(*) FROM mytable WHERE FK_ID=x) as Total,
(SELECT COUNT(*) FROM mytable WHERE FK_ID=x AND REVIEWED=1) as Rejected,
(SELECT COUNT(*) FROM mytable WHERE FK_ID=x AND REJECTED=1) as Reviewed
0
 

Author Comment

by:tfcallahan
Comment Utility
I just had to swap the Reviewed and Rejected expression labels and works like a charm.
Thanks!
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
lol - tired eyes. Must be bed time ;)

Glad to help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
Determine next b-weekly date 12 52
Link SQL table to Webpage 9 34
Report Builder 9 24
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now