Link to home
Start Free TrialLog in
Avatar of mahpog
mahpog

asked on

sql sytnx : include a count for each srp_no in range (2)

Reposting (I was confident that answer given was 100% correct- I was wrong- gave same resultset in original post )

Here is original text:

I would like to have all srp_no's in the "IN" statement with a 0 for those that have no count.
Right now, only get srp_no's tht meet other conditions.

**********************

select srp_no, isnull(count(srp_no),0) as ttlcnt
from asrp_usage
where convert(char(10),convert(datetime,entry_dt), 112) between '20130101' and '20131231'
and frp_ror = '1'
and bus_type_gc = 'G'
and srp_no IN ('14-1','15-1','31-11','31-14','31-15','31-19','31-20','31-21','31-5','31-6','31-9','GB-01','GB-02','GB-06')
group by srp_no
order by srp_no

Resultset:

srp_no      ttlcnt
14-1        6
15-1        5
31-14       1
31-15       5
31-5        3
31-9        1


-Answer given below: I changed to inner, full, right, left  and never get .........
14-1 = 6, 15-1 = 5. 31-11 = 0, 31-14=1, 31-15=0)  - If zero value not included.

***************************

CREATE TABLE #sprno (no varchar(10))

INSERT INTO #sprno (no)
VALUES ('14-1'),('15-1'),('31-11'),('31-14'),('31-15'),('31-19'),('31-20'),('31-21'),('31-5'),('31-6'),('31-9'),('GB-01'),('GB-02'),('GB-06')

-- Then your main query will be something like...
select s.srp_no, isnull(count(u.srp_no),0) as ttlcnt
from #sprno s
   -- LEFT ensures that all #sprno rows are returned
   LEFT JOIN asrp_usage u ON u.spr_no = s.spr_no
where convert(char(10),convert(datetime,u.entry_dt), 112) between '20130101' and '20131231'
and u.frp_ror = '1'
and u.bus_type_gc = 'G'
group by s.srp_no
order by s.srp_no
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mahpog - fyi you can always ask for a clarification in the original question, even if an answer is already accepted.
Avatar of mahpog
mahpog

ASKER

chaau that fixed the problem. removing the where clause. thx.
Avatar of mahpog

ASKER

jim, thanks again.