• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

SQl query help

Hello all,
Please find below the query.How should I modify the query to include a group by clause on "groupby NDC"

 select a.PatName as Patient,a.DrugLabelName as medication,a.dispenseDt as DispensedDate, a.daysSupply, a.PatID, a.NDC, a.RxNo,
   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
2 Solutions
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.
Chris LuttrellSenior Database ArchitectCommented:
Can you post some sample data and desired output?  What is your desired aggregate for the grouping?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read this article, it explaines the "issues" around the GROUP BY / DISTINCT and how to "solve" them:
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.

Join & Write a Comment

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now