Status: Solved
Priority: Medium
Security: Public
Views: 138
Last Modified: 2014-12-12

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.

doc_jay

Asked: 2014-12-12

Advertise Here

MySQL Server

1 Solution

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2014-12-12

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

doc_jayAuthor Commented: 2014-12-12

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

MYSQL - select help with grouping totals

Join & Write a Comment Already a member? Login.

Featured Post