0

So I have the following normal query:

SELECT 
    someCol, 
    COUNT(*), 
    GROUP_CONCAT(myTableID SEPARATOR ',') 
FROM myTable 
GROUP BY someCol

I would like to use the list of myTableIDs in the third column in a subquery's IN clause. The following non-working query illustrates this:

# this does not work
SELECT
    someCol,
    COUNT(*),
    (    SELECT COUNT(mySubTable.*) 
         FROM mySubTable 
         WHERE mySubTable.foreignID IN GROUP_CONCAT(myTable.myTableID SEPERATOR ',')
    ) AS FOREIGN_COUNT
FROM myTable GROUP BY someCol

The IN GROUP_CONCAT() part doesn't work because the GROUP_CONCAT() is just returning a string with commas, not an actual list of ids or a subquery.

My question is, is it possible to use the aggregate list of myTableIDs as part of the IN clause of the subquery?

ericcccc
  • 152
  • 1
  • 5
  • I think you need to get that info in a variable. The do a prepared statement in the language of your choice – Drew Mar 27 '16 at 04:54
  • I am almost embarrassed to show you this, but it is somewhat related. Check out [Appendix C](http://stackoverflow.com/a/34015333) – Drew Mar 27 '16 at 05:05
  • What exactly, in plain English, are you trying to do? What's the actual goal? – Bohemian Mar 27 '16 at 10:38
  • @Bohemian: kordiko's answer solved my problem, but I'll explain using an example. Say you have a Person table and you want to get a list of first names from that table along with a count. That would be like my first query. What I'm trying to do is, for each row of that result, I want to know how many of them live in the USA. For the sake of my situation, let's say the person's country is in a separate many-to-many table on PersonID. In order to get a count of each first name's USA-status, I was trying to pass the list of PersonIDs for each first name to a subquery's IN-clause. Make sense? – ericcccc Mar 27 '16 at 19:41

1 Answers1

1

Use a 'SUM' function do do such a task:

SELECT 
    someCol, 
    COUNT(*), 
    GROUP_CONCAT(myTableID SEPARATOR ','),
    SUM( 
         ( select COUNT(*) FROM mySubTable
           WHERE mySubTable.foreignID = myTable.myTableId )
        )
FROM myTable 
GROUP BY someCol;
krokodilko
  • 35,300
  • 7
  • 55
  • 79