Solved

SQl query help

Posted on 2013-01-09
3
291 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 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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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