By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Using same field as alias and using the count in the query.

P: 4
I am trying to create a small report and what I want to do is pull the same field in a query 4 times and I am trying to alias the field and also have it just count the number of records, not give me the actual record. An example of what I am trying to do is:

SELECT Status AS Approved = Count(Status)
FROM AppStatus
WHERE Status = 'A'
GO
SELECT Status AS Conditioned = Count(Status)
FROM AppStatus
WHERE Status = 'I'

but instead of it actually pulling the records, I want it to just count the records for each, so if Approved has 4 records, I want it to show 4 not all 4 records and if Conditioned has 9 records, I want to see 9 instead of all 9 records, but I also want to see 4 for the Approved field as well. I don't even know if this is possible, but I think my logic is somewhat right so far, I just don't know where to put the count so the results would look like this.


NAME____Approved_____Declined_____Conditioned___To tal
Bob................4.....................2........ ............9......................15

instead, what I have been getting is

NAME____Approved____Declined____Conditioned____Tot al
Bob........1............0.............0..........1
Bob........0............1.............0..........1
Bob........0............1.............0..........1
Bob........1............0.............0..........1
Bob........1............0.............0..........1
Bob........1............0.............0..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1
Bob........0............0.............1..........1



Does this make any sense? Thanks for any help that might come my way. It will be very appreciated. By the way, I am not wanting to get the ..... I had to use those to try to line up my numbers with my columns.
May 5 '11 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,359
You need to use the group by clause. And then pivot it (if MySQL supports pivoting) to get the values across the top.
May 5 '11 #2

P: 4
I will look at the group by clause as well as the pivot. I have never used or seen this, so I am going to see what I can find on it. Thanks.
May 5 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.