October 12, 2012 at 8:00 am
I need to get the counts for the number of records that meet specific criteria. Here's a scenario of what I need to accomplish. I can get one or the other but not both in one query
Provide branch, year, count for individuals who
1. Failed the test
2. Failed the test who are right handed.
So I would end up with
Branch |Year |Count of All who failed |Count of all right handed who failed
Blue|2012 | 120 | 87
Red|2011 | 68 | 7
Blue|2011 | 135 | 65
Red |2012 | 55 | 30
October 12, 2012 at 8:02 am
jon.wilson (10/12/2012)
I need to get the counts for the number of records that meet specific criteria. Here's a scenario of what I need to accomplish. I can get one or the other but not both in one queryProvide branch, year, count for individuals who
1. Failed the test
2. Failed the test who are right handed.
So I would end up with
Branch |Year |Count of All who failed |Count of all right handed who failed
Blue|2012 | 120 | 87
Red|2011 | 68 | 7
Blue|2011 | 135 | 65
Red |2012 | 55 | 30
Hello and welcome to SSC!
I'm afraid that you haven't really given us enough to go on there. Remember, we can't see your data, your expected results or your actual results. So without a reproducible script that shows your problem, there isn't much that anyone can do to help you.
If you could set-up DDL and sample data scripts, along with your expected results, as explained in this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/ <-- then it'd be much easier to help[/url]. Otherwise, we're only guessing 😉
Thanks
October 12, 2012 at 8:11 am
here's a CTE of your data that is inte ready-to-use format my friend Cadavre suggested:
WITH myCTE ([Branch],[Year],[Count of All who failed],[Count of all right handed who failed])
AS
(
SELECT 'Blue ',2012 , 120 , 87 UNION ALL
SELECT 'Red ' ,2011 , 68 , 7 UNION ALL
SELECT 'Blue ',2011 , 135 , 65 UNION ALL
SELECT 'Red ' ,2012 , 55 , 30
)
SELECT * FROM myCTE
now this is a simple grouping TSQL problem, typical of homework, so I'm going to ask you for a little more...
what have you tried so far?
do you understand how a GROUP, with the SUM() functions will help on this issue?
Lowell
October 12, 2012 at 8:17 am
Lowell (10/12/2012)
WITH myCTE ([Branch],[Year],[Count of All who failed],[Count of all right handed who failed])
AS
(
SELECT 'Blue ',2012 , 120 , 87 UNION ALL
SELECT 'Red ' ,2011 , 68 , 7 UNION ALL
SELECT 'Blue ',2011 , 135 , 65 UNION ALL
SELECT 'Red ' ,2012 , 55 , 30
)
SELECT * FROM myCTE
I could be wrong Lowell, but I read the OP as saying that the above is what he wants to end up with as his result-set. He doesn't seem to have mentioned his starting data.
October 12, 2012 at 8:40 am
Cadavre, that is correct. I need to get the results
Here's what I've been able to get so far. I can get the results by running one query or the other but I would like to combine the two so I have one so my results would be:
Branch | Year | Total Failed | Tota failed who are right handed
Select Branch, Year, count (*)
from table
where
fail = true
or
Select Branch, Year, count (*)
from table
where
fail = true and right handed = true
October 12, 2012 at 8:48 am
jon.wilson (10/12/2012)
Cadavre, that is correct. I need to get the resultsHere's what I've been able to get so far. I can get the results by running one query or the other but I would like to combine the two so I have one so my results would be:
Branch | Year | Total Failed | Tota failed who are right handed
Select Branch, Year, count (*)
from table
where
fail = true
or
Select Branch, Year, count (*)
from table
where
fail = true and right handed = true
As I said before then, read this --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D. Supply sample data as is shown in the link so that it is readily consumable for anyone wanting to help. Then show what your expected results are based on the sample data that you have provided.
Here's a total guess for what you're after: -
SELECT Branch, Year,
SUM(CASE WHEN fail = 'true' THEN 1 ELSE 0 END),
SUM(CASE WHEN fail = 'true' AND
= 'true' THEN 1 ELSE 0 END)
FROM table
GROUP BY Branch, Year;
I won't be posting again without the requested sample data.
October 12, 2012 at 9:17 am
Cadavre
That is exactaly what I needed. Thanks a bunch.
October 12, 2012 at 12:24 pm
Cadavre (10/12/2012)
Here's a total guess for what you're after: -
SELECT Branch, Year,SUM(CASE WHEN fail = 'true' THEN 1 ELSE 0 END),
SUM(CASE WHEN fail = 'true' AND
= 'true' THEN 1 ELSE 0 END)
FROM table
GROUP BY Branch, Year;
I won't be posting again without the requested sample data.
Since the one set is necessarily a subset of the other, it will usually be more efficient to filter the records to the superset instead of working with all of the records.
SELECT Branch, Year,
COUNT(*),
SUM(CASE WHEN
= 'true' THEN 1 ELSE 0 END)
FROM table
WHERE fail = 'true'
GROUP BY Branch, Year;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply