CODEWITHSUNDEEP
mysql
StudioTime
StudioTime

Reputation: 23989

Select some but count all MySQL

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?

Upvotes: 0

Views: 99

Answers (5)

Sam
Sam

Reputation: 1366

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.

Upvotes: 0

nbar
nbar

Reputation: 6158

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

Upvotes: 0

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 1

user2422457
user2422457

Reputation: 

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

Upvotes: 4

Martijn
Martijn

Reputation: 16103

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

Upvotes: 1

Related Questions