How to get number of pics and cover pics from one table?

Question

I'm using Apache phoenix. I have table pictures which look like this:

|---------------|------------------|-----------|----------|--------|
|      id       |     picture      |  is_cover | userID   | album  |
|---------------|------------------|-----------|----------|--------|
|          1    |        aaa       |  true     |   1      |  test  |
|---------------|------------------|-----------|----------|--------|
|          2    |        bbb       |  false    |   1      |  test  |
|---------------|------------------|-----------|----------|--------|
|          3    |        ccc       |  false    |   1      |  test1 |
|---------------|------------------|-----------|----------|--------|
|          4    |        ddd       |  true     |   1      |  test1 |
|---------------|------------------|-----------|----------|--------|

I want to get album names, count of pics in album and cover pics of album from specific user. Output should look like this:

|---------------|------------------|-----------|
|     picture   |        album     |  count    |
|---------------|------------------|-----------|
|     aaa       |        test      |  2        |
|---------------|------------------|-----------|
|     ddd       |        test1     |  2        |
|---------------|------------------|-----------|

Answer 1

you can try by using join

    select t1.picture,t1.album,t2.cnt from
     (
     select *  from pictures where is_cover=true
     ) t1        
    join        
      (  select album, count(*) as cnt from pictures
         group by album
      ) t2 on t1.album=t2.album