Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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
0
mahpog
Asked:
mahpog
  • 2
1 Solution
 
chaauCommented:
There is a small problem with the query above. The conditions that control the data in asrp_usage table need to be included into the ON clause, not WHERE clause. The latter restrict the resultset of the whole query, removing the records with 0 count. So, the correct query will be:
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
-- continue to add the conditions to the ON clause, do not use WHERE clause
AND 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 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
mahpog - fyi you can always ask for a clarification in the original question, even if an answer is already accepted.
0
 
mahpogAuthor Commented:
chaau that fixed the problem. removing the where clause. thx.
0
 
mahpogAuthor Commented:
jim, thanks again.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now