Solved

SQl query help

Posted on 2013-01-09
3
296 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
[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
3 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 250 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 26

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 250 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

749 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