March 2, 2012 at 2:00 pm
Hello,
This is my first post this forum. I want to build a query that counts the number of people that are on a particular shift. It is pulling the information from one table. I am able to create a statement that gives the results I am looking for but I can only create one column. As you can see from the SQL Statement below I tried to use the UNION function but it still puts the results under one column. How would I go about getting the results to display in seperate columns?
Thanks
Brian
SELECT COUNT(assignment) AS firstshift
FROM tb_DNM_1_employee
WHERE (assignment = '7') and (rank = '1')
union
SELECT COUNT(assignment) AS secondshift
FROM tb_DNM_1_employee
WHERE (assignment = '8') and (rank = '1')
results of this query
firstshift
15
20
March 2, 2012 at 2:33 pm
You could inline case statements here...
SELECTSUM(CASE WHEN assignment = '7' AND rank = '1' THEN 1 ELSE 0 END) AS firstshift
,SUM(CASE WHEN assignment = '8' AND rank = '1' THEN 1 ELSE 0 END) AS secondshift
,...
FROMtb_DNM_1_employee
March 2, 2012 at 2:34 pm
SELECT (
SELECT COUNT(assignment)
FROM tb_DNM_1_employee
WHERE (assignment = '7') and (rank = '1')
) AS firstshift,
(
SELECT COUNT(assignment)
FROM tb_DNM_1_employee
WHERE (assignment = '8') and (rank = '1')
) AS secondshift
March 2, 2012 at 3:24 pm
Or you could make it more of a table type structure.
SELECT COUNT(assignment) AS ShiftCount, 'FirstShift' as ShiftDescription
FROM tb_DNM_1_employee
WHERE (assignment = '7') and (rank = '1')
union all
SELECT COUNT(assignment), 'SecondShift'
FROM tb_DNM_1_employee
WHERE (assignment = '8') and (rank = '1')
_______________________________________________________________
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/
March 5, 2012 at 7:09 am
Thanks everyone that did the trick. Appreciate the fast response
June 4, 2012 at 3:28 am
hello everyone, please hoe do i use this script to generate multiple rows, this just works if only a row of information is desired. hope to get a reply soon.thanx
June 4, 2012 at 8:18 am
flashykez (6/4/2012)
hello everyone, please hoe do i use this script to generate multiple rows, this just works if only a row of information is desired. hope to get a reply soon.thanx
Please don't hijack other threads. You should start your own thread for your issue. I would suggest that prior to posting you take a look at the first link in my signature for best practices when 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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply