Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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

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
tfcallahan
Asked:
tfcallahan
  • 3
  • 2
1 Solution
 
tfcallahanAuthor Commented:
p.s. The results should look something like:

FK_ID   Total    Rejected   Reviewed
1234    6          1             5
0
 
NightmanCTOCommented:
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
 
NightmanCTOCommented:
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
 
tfcallahanAuthor Commented:
I just had to swap the Reviewed and Rejected expression labels and works like a charm.
Thanks!
0
 
NightmanCTOCommented:
lol - tired eyes. Must be bed time ;)

Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now