Solved

Count based on entries in table-2

Posted on 2014-09-11
7
269 Views
Last Modified: 2014-09-14
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.

How to code it in SQL? Please help.

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

Open in new window

0
Comment
Question by:pvsbandi
7 Comments
 
LVL 18

Expert Comment

by:daveslash
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

Open in new window


HTH,
DaveSlash
0
 

Author Comment

by:pvsbandi
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

by:PortletPaul
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
;

Open in new window

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

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pvsbandi
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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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 |

Open in new window


or this:

|  CITY | COLUMN_1 |
|-------|----------|
| CITY3 |        2 |

Open in new window

0
 
LVL 24

Accepted Solution

by:
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 

Open in new window


Regards,
   Tomas Helgi
0
 

Author Closing Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now