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(d atetime,en try_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-1 5','31-19' ,'31-20',' 31-21','31 -5','31-6' ,'31-9','G B-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(d atetime,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
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(d
and frp_ror = '1'
and bus_type_gc = 'G'
and srp_no IN ('14-1','15-1','31-11','31
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'
-- 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(d
and u.frp_ror = '1'
and u.bus_type_gc = 'G'
group by s.srp_no
order by s.srp_no
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mahpog - fyi you can always ask for a clarification in the original question, even if an answer is already accepted.
ASKER
chaau that fixed the problem. removing the where clause. thx.
ASKER
jim, thanks again.