Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQl query help

Posted on 2013-01-09
3
Medium Priority
?
316 Views
Last Modified: 2013-01-10
Hello all,
Please find below the query.How should I modify the query to include a group by clause on "groupby NDC"
Thanks.

 select a.PatName as Patient,a.DrugLabelName as medication,a.dispenseDt as DispensedDate, a.daysSupply, a.PatID, a.NDC, a.RxNo,
   a.RoNo,d.Sig
   from (select * from Rx..HRxs union all select * from FwArchive..HRxs)
   as a
   left join HRxDirections as d on a.PatID = d.PatID and a.FacID = d.FacID and a.RxNo = d.RxNo and a.RoNo = d.RoNo and a.Posted =d.Posted
   left outer join Pat..Patients as b on a.PatID = b.PatID and a.FacID = b.FacID
   left outer join Fac..NursingStations as c on b.NsID = c.NsID and a.FacID = c.FacID
    where a.FacId = '74' and a.DispenseDt >= '07/01/2012'  and a.DispenseDt <= '01/09/2013'  and a.PatId = '22'
     and TransType in ('P', 'R', 'U', 'Q', 'B') order by  a.DrugLabelName,a.DispenseDt
0
Comment
Question by:Star79
3 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 1000 total points
ID: 38760831
You can't group by just one column of the SELECT, unless you have agregate functions for the rest of the columns (like if you were grouping by a patient name and putting sums of columns on order records or something). It sounds like you just want to sort the data by NDC and if that is the case change the "order by" line to "order by a.NDC, a.DrugLabelName, a.DispenseDt".

Also you may wish to put NDC at the start of the SELECT just so it makes more sense. But doesn't have to be.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38761463
Can you post some sample data and desired output?  What is your desired aggregate for the grouping?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38762178
please read this article, it explaines the "issues" around the GROUP BY / DISTINCT and how to "solve" them:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 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