May 3, 2012 at 10:40 am
***Edited because of the common sense suggestion that I actually demonstrate what I'm working with 🙂 *** See below.
May 3, 2012 at 10:51 am
It is totally unclear what your question is here. You need to more clearly explain what you are trying to do. If you want help with the actual code you will need to post ddl, sample data and desired output. Take a look at the first link in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2012 at 12:22 pm
Here's an example table:
CREATE TABLE Start
(
Start_IDCHAR(10)NOT NULL,
School_State_StartCHAR(25)NOT NULL,
)
;
INSERT INTO Start
(
Start_ID,
School_State_Start
)
VALUES
('1234','Ready'),
('5678','Ready'),
('6546','Ready'),
('9874','Ready'),
('6510','Ready'),
('9871','Ready'),
('3333','Soon'),
('1111','Soon'),
('2222','Soon'),
('4444','Soon'),
('5555','Soon'),
('6666','Soon'),
('7777','Soon'),
('9876','Someday'),
('9999','Someday'),
('1298','Someday'),
('4321','Someday')
Here is the query I used to stack the results in SSMS to make analysis easier
SELECT 'Ready' AS 'Current Cycle', COUNT(DISTINCT.Start.Start_ID) AS Count
FROM Start
WHERE Start.School_State_Start = 'Ready'
UNION ALL
SELECT 'Soon' AS 'Current Cycle', COUNT(DISTINCT.Start.Start_ID) AS Count
FROM Start
WHERE Start.School_State_Start = 'Soon'
UNION ALL
SELECT 'Someday' AS 'Current Cycle', COUNT(DISTINCT.Start.Start_ID) AS Count
FROM Start
WHERE Start.School_State_Start = 'Someday'
This query prints the name of School_State_Start and then counts the distinct instances of the Start_ID per School_State_Start. However, I'm wanting to put School_State_Start in the column, and count the distinct Start_ID's in the row immediately beneath it. Something like this:
Ready Soon Someday
6 7 4
I've played around with a few things, but haven't come up with a good solution just yet.
May 4, 2012 at 12:41 pm
Just thought I would bump this once to see if anyone had a suggestions. If not, no problem.
May 4, 2012 at 1:04 pm
Does the following do what you want?
SELECT SUM(CASE WHEN School_State_Start = 'Ready' THEN 1 ELSE 0 END) AS Ready
,SUM(CASE WHEN School_State_Start = 'Soon' THEN 1 ELSE 0 END) AS Soon
,SUM(CASE WHEN School_State_Start = 'Someday' THEN 1 ELSE 0 END) AS Someday
FROM Start
Dave
May 4, 2012 at 1:21 pm
Dave beat me to it. You either pivot or crosstab, and he's provided an excellent example of the crosstab.
Pivots tend to be slower optimization-wise, and they're not necessarily easier, either.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply