Link to home
Start Free TrialLog in
Avatar of doc_jay
doc_jay

asked on

MYSQL - select help with grouping totals

MYSQL  5.6.22

  Hi,
   
     I'm hoping I can get this query to group my totals correctly.

 - All of the data I need to search for lives in one table -

select max(study_custom1) as 'Outside Institution', COUNT(study_custom1) as 'Number of Studies' 
from pacsdb.study
where accession_no like '%OUT%'
and study_custom1 is not null
and created_time >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ) 
AND created_time < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
group by study_custom1
order by 'Number of Studies' DESC

Open in new window


this result comes back with:

site3   5
site2   8
site8   2
site4    1
site1   12

Open in new window


I would like for the results to show the site with the most 'counts' to be at the top of the list.
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of doc_jay
doc_jay

ASKER

Thanks, that worked as I expected.  I always thought I needed a 'DISTINCT before 'study_custom1' in my first line after select.