Select some but count all MySQL

Question

I have a table e.g.

Artist     Title      Song Key   Easytosing
A          Title A    A          No
A          Title A    B          Yes
A          Title A    F          Yes
B          Title B    A          Yes
C          Title C    F#         No

I want to return each individual song that is tagged 'easytosing' but also show how many versions there are of that song a) easytosing b) total e.g. Ideal results would be:

Artist     Title       How_many_tot    How_many_easy
A          Title A     3               2
B          Title B     1               1

I can show how many are easy to sing using:

SELECT *, count(*) as How_many_easy from tracks
where easytosing='Yes' 
group by artist,title 
order by artist asc

Is there a way I can show both so the query only selects the easytosing but counts all?

this might help http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query — Deepanshu Goyal, Sep 02 '13 at 09:24

score 4 · Answer 1 · answered Sep 02 '13 at 09:20

4

select count(*) HOW_MANY_TOT,
       sum(case EASYTOSING when 'Yes' then 1
                else 0
            end case) HOW_MANY_EASY
       ARTIST,
       TITLE
  from TRACKS
 group by ARTIST, TITLE
having HOW_MANY_EASY > 0

answered Sep 02 '13 at 09:20

`HAVING HOW_MANY_EASY >0` ;) – Jakub Matczak Sep 02 '13 at 09:21

score 1 · Answer 2 · answered Sep 02 '13 at 09:19

I suggest you make two queries, one for the data, one for the count. You are useing select * which means you are selecting the whole table, which is alot of overhead if you only need a number.

I have build a webshop where a similar situation, two queries turned out to be MUCH faster and allot easier to maintain. The count can be optimized by only selecting 1 column and dropping the order by.

Another solution is to use a subquery for the count_value. A subquery does not support a limit, but you dont need that for the count. Just select as minimal as possible without a ordering

score 1 · Accepted Answer · answered Sep 02 '13 at 09:22

1

SELECT
  Artist,
  Title,
  COUNT(*) How_many_tot,
  SUM(Easytosing='yes') how_many_easy
FROM
  tracks
GROUP BY
  Artist, Title
HAVING
  SUM(Easytosing='yes')>0

Please see fiddle here.

answered Sep 02 '13 at 09:22

fthiella

48,073
15
90
106

Sam · Answer 4 · 2013-09-02T10:21:30.163

Have you tried making it a subquery and adding a where clause?

SELECT countTable.*, 
       count(*) as How_many_easy
FROM 
(
    SELECT Artist, 
           Title, 
           MAX(easytosing) as easytosing,
           count(*) as How_many_total 
    from tracks
    group by artist,title 
) countTable ON tracks.Artist
where easytosing='Yes'
group by artist,title 
order by artist asc

Note: the MAX(easytosing) should return 'Yes' if such a record exists and 'No' otherwise.

score 0 · Answer 5 · answered Sep 02 '13 at 09:34

You have to add an unique ID (auto_increment, in my query it calls ID) to your talbe, then u can get the infos u want with:

select tall.artist, tall.title, count(distinct tall.ID) as total, count(distinct teasy.ID) as easy from tracks as tall
left join tracks as teasy on teasy.artist=tall.artist and teasy.title=tall.title and teasy.easytosing='Yes'
group by tall.artist, tall.title

Select some but count all MySQL

5 Answers5