November 30, 2015 at 8:21 am
Hi,
I have two tables namely events and subinfo in sql server 2012. The events has site information and subject number for various events and the subinfo will have site information and subject number.
I am finding the rate of events by calculating count of subjects in events table / count of subjects in subinfo table.
below is my sqlcode
SELECT A.SITEID
,AeSubCount
,SubCnt
,ROUND(( CAST(AeSubCount AS FLOAT) / CAST(SubCnt AS FLOAT) ), 2) AS Rate
FROM ( SELECT dm.SITEID
,COUNT(DISTINCT A.USUBJID) AS AeSubCount
FROM dbo.tbl_events AS A
INNER JOIN tbl_subinfo dm ON A.USUBJID = dm.USUBJID
GROUP BY dm.SITEID
) A
,( SELECT d.SITEID
,COUNT(DISTINCT USUBJID) AS SubCnt
FROM tbl_subinfo AS d
GROUP BY d.SITEID
) B
WHERE A.SITEID = B.SITEID
The code is working fine. However i am trying to optimize the code by removing the two sub queries I have used so that I can prevent the hash match taking up more cost.
Any ideas please...
November 30, 2015 at 9:13 am
This should be equivalent, but I don't have data to test.
SELECT A.SITEID
,AeSubCount
,SubCnt
,ROUND(( CAST(AeSubCount AS FLOAT) / CAST(SubCnt AS FLOAT) ), 2) AS Rate
FROM ( SELECT dm.SITEID
,COUNT(DISTINCT dm.USUBJID) AS AeSubCount
,COUNT(DISTINCT A.USUBJID) AS SubCnt
FROM dbo.tbl_events AS A
RIGHT JOIN tbl_subinfo dm ON A.USUBJID = dm.USUBJID
GROUP BY dm.SITEID
) A
November 30, 2015 at 9:41 am
Hi,
Thanks for the solution. I'm getting the error
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
when i run the code you provided. However there is no 0 value in the output that is returned by my query.
I think the right join is not working fine.
i have attached the data from my two tables for your reference.
November 30, 2015 at 9:59 am
you'll need to add the NULLIF function, i think:
,ROUND(( CAST(AeSubCount AS FLOAT) / CAST(NULLIF(SubCnt,0) AS FLOAT) ), 2) AS Rate
Lowell
November 30, 2015 at 10:01 am
You have 2 options. One is exactly the same as the query that you have, and the other one will bring additional rows. I won't tell you which is which so you'll have to understand and test both queries to know what they do and what they need.
SELECT A.SITEID
,AeSubCount
,SubCnt
,ROUND(( CAST(AeSubCount AS FLOAT) / CAST(SubCnt AS FLOAT) ), 2) AS Rate
FROM ( SELECT dm.SITEID
,COUNT(DISTINCT dm.USUBJID) AS AeSubCount
,COUNT(DISTINCT A.USUBJID) AS SubCnt
FROM dbo.tbl_events AS A
RIGHT JOIN tbl_subinfo dm ON A.USUBJID = dm.USUBJID
GROUP BY dm.SITEID
HAVING COUNT(DISTINCT A.USUBJID) > 0
) A
ORDER BY SITEID
SELECT A.SITEID
,AeSubCount
,SubCnt
,ROUND(( CAST(AeSubCount AS FLOAT) / CAST(NULLIF( SubCnt, 0) AS FLOAT) ), 2) AS Rate
FROM ( SELECT dm.SITEID
,COUNT(DISTINCT dm.USUBJID) AS AeSubCount
,COUNT(DISTINCT A.USUBJID) AS SubCnt
FROM dbo.tbl_events AS A
RIGHT JOIN tbl_subinfo dm ON A.USUBJID = dm.USUBJID
GROUP BY dm.SITEID
) A
ORDER BY SITEID
By the way, your test data didn't help much because it wouldn't return any error and I had to change it into INSERT statements to use it. This is an example on how you should post your sample data.
CREATE TABLE tbl_events (SITEID int, USUBJID varchar(20));
INSERT INTO tbl_events VALUES(1051, '2060-1022');
INSERT INTO tbl_events VALUES(1024, '2060-1026');
INSERT INTO tbl_events VALUES(1024, '2060-1026');
INSERT INTO tbl_events VALUES(1024, '2060-1026');
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply