SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to fill in "gaps" when you GROUP BY?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 08/15/2011 :  12:44:20  Show Profile  Reply with Quote
Hi again, here's what I'm doing:
------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#EmpTenure') IS NOT NULL
	DROP TABLE #EmpTenure
------------------------------------------------------------------------------
CREATE TABLE #EmpTenure
(
	emp_id			INT
	, months_tenure		INT
	, term_date		DATETIME
	, PRIMARY KEY (emp_id)
)

INSERT INTO #EmpTenure SELECT 11111, 3, '1/1/2011'
INSERT INTO #EmpTenure SELECT 22222, 3, '2/2/2011'
INSERT INTO #EmpTenure SELECT 33333, 5, '3/3/2011'
INSERT INTO #EmpTenure SELECT 44444, 6, '4/4/2011'
INSERT INTO #EmpTenure SELECT 55555, 8, '5/5/2011'
INSERT INTO #EmpTenure SELECT 66666, 8, '6/6/2011'
INSERT INTO #EmpTenure SELECT 77777, 8, '7/7/2011'
------------------------------------------------------------------------------
SELECT
	e.months_tenure
	, [total_employees] = Count(*)
FROM #EmpTenure e
WHERE term_date <= getdate()
GROUP BY e.months_tenure
ORDER BY e.months_tenure
------------------------------------------------------------------------------

Of course, the real table has more fields and records, but this is just what I'm looking at right now.

The result is:
months_tenure     total_employees
---------------------------------
      3                  2
      5                  1
      6                  1
      8                  3

And what I'd like is:
months_tenure     total_employees
---------------------------------
      3                  2
      4                0
      5                  1
      6                  1
      7                0
      8                  3

My question is: Is there a way to fill in those gaps in months_tenure (4 and 7, in the example) with a total_employees value of 0? The reason I ask is that I'm copying the results into Excel and making a graph, but the graph is a little misleading because there are never any zeroes displayed.

Thanks, as always!

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/15/2011 :  13:20:22  Show Profile  Visit russell's Homepage  Reply with Quote
Here's one way.

DECLARE @t TABLE (months_tenure INT)
INSERT	@t
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8

SELECT	t.months_tenure, [total_employees] = Count(e.emp_id)
FROM	@t t
LEFT JOIN (
	SELECT	emp_id, months_tenure
	FROM	#EmpTenure
	WHERE	term_date <= getdate()
) e
On	e.months_tenure = t.months_tenure
GROUP	BY t.months_tenure
ORDER	BY t.months_tenure
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 08/15/2011 :  13:36:54  Show Profile  Reply with Quote
Oh, so basically just join to a table of integers? Would it be wise to have a permanent table? My example only went up to 8, but I think I may need it to go over 100 (which I'd rather not have to do more than once). Is there a way to create a table of integers quickly without having to UNION ALL a gazillion times?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/15/2011 :  13:51:27  Show Profile  Visit russell's Homepage  Reply with Quote
Create Table ints (i INT Primary Key);
GO

Declare @i int
SET	@i = 1
WHILE	@i <= 100
BEGIN
	INSERT ints VALUES(@i)
	SET @i = @i + 1
END
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 08/15/2011 :  13:51:44  Show Profile  Visit webfred's Homepage  Reply with Quote
Have a look here for example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102936
on how to use master..spt_values


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/16/2011 :  10:03:20  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
And there is a fun CTE Tally in Fig.7: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Corey

I Has Returned!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000