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

[Jason Stinson]

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.

[Rabbit]

You need to use the group by clause. And then pivot it (if MySQL supports pivoting) to get the values across the top.

[Jason Stinson]

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.