0

I am trying to find count of field by another field in same table on MySQL.My Table Like This:

Id          DrgId    CodeType IsPrincipal Code
182250051   48261836    I     1           T151  
182250055   48261836    I     2           U739  
182250059   48261836    I     3           Y929  
182250061   48261836    I     4           W444  
182250062   48261836    A     2           3006104

So I want to find used helper codes for T151 which is IsPrincipal equals 1.

Output should like this:

Code  Helper_I_Count   Helper_A_Count
T151   3               1

So I tried Like this:

SELECT t.`Code`,COUNT(v1.`Code`) AS EkTaniSay,COUNT(v2.`Code`) AS IslemSay
FROM TIGPatientCode t,
(
SELECT DRGPatientId,`Code`
FROM TIGPatientCode
WHERE IsPrincipal<>'1' AND CodeType='I'
) v1,
(
SELECT DRGPatientId,`Code`
FROM TIGPatientCode
WHERE IsPrincipal<>'1' AND CodeType='A'
) v2
WHERE t.IsPrincipal='1' AND t.DRGPatientId=v1.DRGPatientId AND t.DRGPatientId=v2.DRGPatientId
GROUP BY t.`Code`

But it wont get actual count.

How can I Do this?

Thanks

tcetin
  • 979
  • 1
  • 21
  • 48
  • am not sure what the logic to follow here? can you explain a bit more – Rahul Nov 24 '16 at 08:22
  • There's only 1 `T151` row, how are you getting counts of 3 and 1 for the two code types? – Barmar Nov 24 '16 at 08:24
  • @Barmar, in that case can you justify your dupe hammer close – Rahul Nov 24 '16 at 08:24
  • @Rahul I'm assuming he just showed part of the data. – Barmar Nov 24 '16 at 08:25
  • @Barmar, but per SO policy, dupe hammer shouldn't be used unless you are totally sure that it's indeed a dupe. Your dupe close looks invalid to me. – Rahul Nov 24 '16 at 08:26
  • OK, I've reopened it. But I'll bet you anything it was the right dupe when we get clarification. I'm very good at decoding poorly-written questions. – Barmar Nov 24 '16 at 08:27
  • @Barmar This is example data. The count is not T151 count.The count is code which depent on T151.So T151 is main code and I Want to calculate code which not main code grouped by main code. – tcetin Nov 24 '16 at 08:28
  • @Barmar, LOL and am pretty sure you are correct but just to be sure. Thank You for reopening it. – Rahul Nov 24 '16 at 08:28
  • @kodcu So what is it grouped by? Is it grouped by `DrgId`, and you show the code for the row in the group with `IsPrincipal = 1`? – Barmar Nov 24 '16 at 08:29
  • See the linked question for how to get the counts in columns. Then join it with a subquery that gets the principle code for the same `DrgId`. – Barmar Nov 24 '16 at 08:30
  • I will edit the question what I have tried :).After I this it will clear. – tcetin Nov 24 '16 at 08:31

1 Answers1

1
SELECT t2.Code, 
        SUM(t1.CodeType = 'I') AS EkTaniSay,
        SUM(t1.CodeType = 'A') AS IslemSay
FROM TIGPatientCode AS t1
RIGHT JOIN TIGPatientCode AS t2 ON t1.DrgPatientId = t2.DrgPatientId
WHERE t1.isPrincipal != 1 AND t2.isPrincipal = 1
GROUP BY t1.DrgPatientId;

The first part of the query is based on multiple query same table but in different columns mysql. Then I join this with the table again to get the code for the principal row.

The problem with your query is that joining the two subqueries creates a cross-product of all the rows, which causes the counts to be multiplied. Also, if there's any group that doesn't have one of the codes, that subquery will return no rows, so the join will be empty for that code. You could fix the first problem by doing the counts in the subqueries rather than the main query. The second problem can be fixed by using LEFT JOIN. So the fixed version of your query would look like:

SELECT t.Code, v1.EkTaniSay, v2.IslemSay
FROM TIGPatientCode t
LEFT JOIN (
    SELECT DRGPatientId, COUNT(*) AS EkTaniSay
    FROM TIGPatientCode
    WHERE IsPrincipal<>'1' AND CodeType='I'
    GROUP BY DRGPatientId
) AS v2 ON t.DRGPatientId = v2.DRGPatientId
LEFT JOIN (
    SELECT DRGPatientId, COUNT(*) AS IslemSay
    FROM TIGPatientCode
    WHERE IsPrincipal<>'1' AND CodeType='A'
    GROUP BY DRGPatientId
) AS v1 ON t.DRGPatientId = v1.DRGPatientId
WHERE t.IsPrincipal = 1

DEMO

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612