Solved

MYSQL - select help with grouping totals

Posted on 2014-12-12
2
122 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.
0
Comment
Question by:doc_jay
2 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40497583
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
 

Author Closing Comment

by:doc_jay
ID: 40497591
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php mysql if then statement syntax 4 41
mysql Encryption with PHP 8 103
Excel - SQL export question 3 42
INDEX does not make a difference, why? 10 51
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question