• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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.
0
doc_jay
Asked:
doc_jay
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:
select 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 COUNT(study_custom1)  DESC

Open in new window

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now