Solved

MYSQL - select help with grouping totals

Posted on 2014-12-12

MySQL Server

1 solution

124 Views

Last Modified: 2014-12-12

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.

Comment

Question by:doc_jay

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 34

Accepted Solution

by:Mike Eghtebas

Mike Eghtebas earned 500 total points

ID: 404975832014-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

Author Closing Comment

by:doc_jay

ID: 404975912014-12-12

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title	# Comments	Views	Activity
I have many databases and one main one, how can I take there column names from the main one?	4	88	2017-01-20
java mysql insert application	14	68	2017-03-20
MySQL stored procedure returning null values	4	36	2017-04-17
Mysql query one to many	11	38	2017-05-15

PHP Client Registration, Login, Logout and Easy Access Control

Article by: Ray

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites. The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided. Of cou…