return counts of zero as well - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

return counts of zero as well

Author

Topic

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 13:06:11

How can i return the counts of zero as well. I am trying to see what sym's have not been referenced in a period of time.

The prob_ctg table holds all the syms defined. its about 1400 and i am currently returning 1052 rows.

SELECT prob_ctg.sym, COUNT(*) AS [Number of Records]
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category
WHERE (call_req.open_date > '1318777793')
GROUP BY prob_ctg.sym
ORDER BY [Number of Records]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-16 : 13:14:37

You might be able to change move WHERE clause to part of the join clause. If not, they it'd help to post sample data and expected output in a consumable format:

SELECT prob_ctg.sym, COUNT(*) AS [Number of Records]
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category
AND (call_req.open_date > '1318777793')
GROUP BY prob_ctg.sym
ORDER BY [Number of Records]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 13:15:16

Don't use RIGHT JOINS....there's no reason..just confuses thing when it gets deep in here


	SELECT a.sym, COUNT(*) AS [Number of Records]
	  FROM prob_ctg a
 LEFT JOIN (SELECT * FROM call_req WHERE call_req.open_date > '1318777793') b
		ON a.persid = b.category
  GROUP BY a.sym
  ORDER BY [Number of Records]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 14:38:43

Thanks for the help, but neither of the above came me the expected results.

call_req:
pcat:66442413
pcat:66443296
pcat:66442877
pcat:66443138
pcat:66443357

prob_ctg:
Software pcat:5100 1
old.Hardware pcat:5101 1
Networks pcat:5102 1
Applications pcat:5103 1
Software.Environment pcat:5104 1

looking to have :
sym count
software 0
old.hardware 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 14:50:27

can you explain how you got those end values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 15:03:39

the end value should come from the count(*) followed by the group by on sym

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 15:10:38

nope. I understood that. What I was asking is how you got those counts from posted data? how did softcare count came as 0 when you've an occurance for software in prob_ctg

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 15:25:55

ohh i was just giving an example of what a zero would be expected to look like. Your right from the data it would not be zero.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 15:39:30

how about



	SELECT a.persid, COUNT(*) AS [Number of Records]
	  FROM prob_ctg a
 LEFT JOIN (SELECT * FROM call_req WHERE call_req.open_date > '1318777793') b
		ON a.persid = b.category
  GROUP BY a.persid
  ORDER BY [Number of Records]

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 15:59:02

nope that did not work. It did not show the zero counts and i checked a pcat that should have had 6 records and it did not.

i used to find a test case:
SELECT *
FROM [mdb].[dbo].[call_req]
where category = 'pcat:66442378' and open_date > '1318777793'
order by open_Date

This has gotten me the closest, but my counts are still wrong:

SELECT prob_ctg.sym, COUNT(call_req.ref_num) AS [Number of Records], prob_ctg.del
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category and open_date > '1318777793'
GROUP BY prob_ctg.sym, prob_ctg.del
HAVING (prob_ctg.del = 0)
ORDER BY [Number of Records]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 16:03:53

unless you clearly show us the data from table and explain us how you want counts to come i dont think anybody will be able to help you out. Check the link in Bretts signature and post data in required format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 16:20:50

call_req:
category open_date
pcat:5100 1318777790
pcat:5100 1318777743
pcat:5100 1318777791
pcat:5101 1318777791
pcat:5102 1318777794
pcat:5103 1318777791

prob_ctg:
sym del
Software pcat:5100 0
old.Hardware pcat:5101 0
Networks pcat:5102 0
Applications pcat:5103 0
Software.Environment pcat:5104 0

Senerio where del =0 and open_date is less than 1318777793 show count of records for each sym value

software.enviornment 0
applications 1
Software 3
old.Hardware 1
Networks 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 16:34:43

[code]
SELECT pc.sym,
COUNT(cr.category) AS Cnt
FROM prob_ctg pc
LEFT JOIN call_req cr
ON pc.pcat = cr.pcat
AND cr.open_date < 1318777793
WHERE pc.del=0
GROUP BY pc.sym
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 16:35:50

What are the Extra 0's for????..wait is this one col value? Software pcat:5100???

You need to normalize your data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 16:36:51

I thought it was this



CREATE TABLE #call_req (
	  category	varchar(25)
	, open_date	int			--???  what kind o date is this?
)

CREATE TABLE #prob_ctg (
	  sym		varchar(25)
	, del		varchar(25)
)
GO

INSERT INTO #call_req (category, open_date)
SELECT 'pcat:5100','1318777790' UNION ALL
SELECT 'pcat:5100','1318777743' UNION ALL
SELECT 'pcat:5100','1318777791' UNION ALL
SELECT 'pcat:5101','1318777791' UNION ALL
SELECT 'pcat:5102','1318777794' UNION ALL
SELECT 'pcat:5103','1318777791'

INSERT INTO #prob_ctg (sym, del)
SELECT 'Software',				'pcat:5100' UNION ALL -- 0
SELECT 'old.Hardware',			'pcat:5101' UNION ALL --  0
SELECT 'Networks',				'pcat:5102' UNION ALL --  0
SELECT 'Applications',			'pcat:5103' UNION ALL --  0
SELECT 'Software.Environment',	'pcat:5104' -- 0

nbritton
Starting Member

22 Posts

Posted - 2012-03-16 : 16:45:53

Yes that was one field. The date value is en epoch format. I was able to get it with the following statement.

SELECT prob_ctg.sym, COUNT(call_req.ref_num) AS [Number of Records], prob_ctg.del
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category and (open_date > '1318777793')
GROUP BY prob_ctg.sym, prob_ctg.del
HAVING (prob_ctg.del = 0)
ORDER BY [Number of Records]

Thanks to both of you. I really appricate the help. Your posts help me get to the end result.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 16:52:16

What the hell is persid?????

Kinda would have been useful

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DeanT
Starting Member

13 Posts

Posted - 2012-03-16 : 16:57:21

default it to zero prior to sending back the other values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources