Solved

# Count based on entries in table-2

Posted on 2014-09-11
269 Views
Hi,

I have two tables like this. First table has City, Case_ID and Client_ID.
Second table has Case_ID and Client_ID.
I don't want to count the Case_ID, if there is atleast 1 CASE_ID, Client_ID combination existing in Table-2.
Example: For the Case_ID = 111, there is 1 match where Client_ID 345 is existing in Table-1 and Table-2. So, i don't want to count the Case_ID 111 in my count.

``````TABLE-1
CITY	CASE_ID		CLIENT_ID
CITY1		111			345
CITY1		111			346
CITY2		222			347
CITY3		333			348
CITY3		333			349
CITY3		444			350

TABLE-2
CASE_ID		CLIENT_ID
111			345
222			347

RESULT

CITY COUNT(DISTINCT CASE_ID)
CITY3	2
``````
0
Question by:pvsbandi

LVL 18

Expert Comment

ID: 40317839
If I'm understanding you correctly, you want the breakdown of the quantity of caseId in table1 where there's no match in table2 (on city and caseId). Is that correct?

If so, then this should work.

``````select d.caseid,
count(*)
from table1 d
where (city, caseId) not in (
select city,
caseid
from table2)
group by d.caseId

CASEID     COUNT ( * )
333               2
444               1
``````

HTH,
DaveSlash
0

Author Comment

ID: 40317956
Dave, that's not exactly what i 'm looking for.
If there is atleast 1 Client_ID for that case_id in both table_1 and Table_2, then i don't want to see it/count it.
0

LVL 48

Expert Comment

ID: 40318677
This provides the expected result (City3 , 3)
``````SELECT
table1.CITY
, COUNT(*)
FROM table1
LEFT JOIN Table2
ON table1.CASE_ID = table2.CASE_ID
WHERE table2.CASE_ID IS NULL
GROUP BY
table1.CITY
;
``````
More detail:
``````    CREATE TABLE Table1
([CITY] varchar(5), [CASE_ID] int, [CLIENT_ID] int)
;

INSERT INTO Table1
([CITY], [CASE_ID], [CLIENT_ID])
VALUES
('CITY1', 111, 345),
('CITY1', 111, 346),
('CITY2', 222, 347),
('CITY3', 333, 348),
('CITY3', 333, 349),
('CITY3', 444, 350)
;

CREATE TABLE Table2
([CASE_ID] int, [CLIENT_ID] int)
;

INSERT INTO Table2
([CASE_ID], [CLIENT_ID])
VALUES
(111, 345),
(222, 347)
;

**Query 1**:

SELECT
table1.CITY
, COUNT(*)
FROM table1
LEFT JOIN Table2
ON table1.CASE_ID = table2.CASE_ID
--                        AND table1.CLIENT_ID = table2.CLIENT_ID
WHERE table2.CASE_ID IS NULL
GROUP BY
table1.CITY

**[Results][2]**:

|  CITY | COLUMN_1 |
|-------|----------|
| CITY3 |        3 |

[1]: http://sqlfiddle.com/#!3/77e0d/4
``````
0

Author Comment

ID: 40319263
Thanks. Ironically, i have missed documenting a scenario that will can have a Client_ID, Case_ID in Table2 that's not in Table1. Below example.
Table1 has Case_ID = 555 and Client_id = 351
Table2 has Case_ID = 555 and Client_id = 355.
In this case, we need to show Case_ID = 555 also in the result.

Example:
``````CREATE TABLE Table1
([CITY] varchar(5), [CASE_ID] int, [CLIENT_ID] int)
;

INSERT INTO Table1
([CITY], [CASE_ID], [CLIENT_ID])
VALUES
('CITY1', 111, 345),
('CITY1', 111, 346),
('CITY2', 222, 347),
('CITY3', 333, 348),
('CITY3', 333, 349),
('CITY3', 444, 350),
('CITY4', 555, 351)
;

CREATE TABLE Table2
([CASE_ID] int, [CLIENT_ID] int)
;

INSERT INTO Table2
([CASE_ID], [CLIENT_ID])
VALUES
(111, 345),
(222, 347),
(555, 355)
;

Result:
RESULT

CITY COUNT(DISTINCT CASE_ID)
CITY3	2
CITY4      1
``````
0

LVL 48

Expert Comment

ID: 40319386
I'm afraid I cannot see any sense to these requirements, perhaps someone else can reconcile them

http://sqlfiddle.com/#!3/d3c4d/2

I can get this:

``````|  CITY | COLUMN_1 |
|-------|----------|
| CITY1 |        1 |
| CITY3 |        2 |
| CITY4 |        1 |
``````

or this:

``````|  CITY | COLUMN_1 |
|-------|----------|
| CITY3 |        2 |
``````
0

LVL 24

Accepted Solution

Tomas Helgi Johannsson earned 500 total points
ID: 40321626
Hi!

How about this query ? It's a bit messy but I think this is close to the results you are looking for

``````select t3.city, t3.case_id, count(t3.client_id) clientIDs
from table1 t3
where t3.case_id in (
select t4.case_id from table1 t4
intersect
select t.case_id from (
select t1.case_id, count(t2.client_id) clientIDs
from table1 t1,table2 t2
where t1.case_id = t2.case_id
and t1.client_id = t2.client_id
group by t1.case_id
having count(t2.client_id) >= 1
)t
)
group by t3.city, t3.case_id
``````

Regards,
Tomas Helgi
0

Author Closing Comment

ID: 40321794
Thanks! That is very close. I can work the rest out.
0

## Featured Post

### Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.