February 11, 2012 at 3:26 am
Hello,
Sorry I'm not posting in a table form, I don't create them so I'm not fluent in the lingo and it would take me forever (I am a SQL newbie!). I should be able to figure out this problem but I seem to have brain block. A simple example would be: I have selected out two columns with many rows. colA= text and colB= numbers like this:
colA, colB
aaa,2
aaa,3
aaa,3
bbb,0
bbb,1
bbb,1
Now I need to know aaa = 8 and bbb = 2, and repeat this count for each distinct colA value. It seems like a simple issue but dang it, it escapes me completely tonight. Any help would be great.
Thanks.
February 11, 2012 at 3:57 am
pharmboy4u (2/11/2012)
Hello,Sorry I'm not posting in a table form, I don't create them so I'm not fluent in the lingo and it would take me forever
No it wouldn't. Make a simple create table statement using 2 columns and then INSERT/SELECT UNION ALL statements. If you're really a newbie, the practice will actually do you good. 😉
For a little assistance in the matter, please see the first link in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2012 at 4:26 am
Well this is my first time ever, and I'm looking at someone else's code. But, just for you Jeff!
create table tab(
colA varchar(7),
colB int)
go
insert into tab(colA,colB)
select 'aaa','2' union all
select 'aaa','3' union all
select 'aaa','3' union all
select 'bbb','0' union all
select 'bbb','1' union all
select 'bbb','1'
select * from tab
I've tried changing some things around but like I said I'm not too hip. After you're done laughing :-), I get this:
Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'tempdb'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tab'.
February 11, 2012 at 4:31 am
Are you connected to Tempdb? Don’t you have any other database to create a table? Does the user account (that you are using) have rights to create a database? If yes, please create a new database & try creating a table in it.
Tempdb is special purpose database & you shouldn’t use it (this way, at least).
February 11, 2012 at 4:44 am
No I do not have rights to create a table. I'm a pharmacist working at a hospital. I only have access to our health care operating systems database to run queries, nothing else really. I'm just a query hacker gathering info for my pharmacy buddies since the built-in reporting features of the operating system are not as capable as data-mining all the info in the SQL database that stores all the info in 6000+ tables. So that's my practice playground, but I have minimal rights matter what choice I use.
February 11, 2012 at 5:02 am
I'm just a query hacker...
I believe there is serious Lingo Issue & you don’t mean what you have written. Still, I am not very comfortable in suggesting you anything at the moment. Please ask for more permission to Hospital DBA. If your tasks / requirements are legitimate, he will provide you necessary access or he will do it himself.
February 11, 2012 at 5:23 am
Don't mistake the word hacker, if that's the problem. I'm certain of what I said. I cannot create or delete or update or do any administrator function in SQL in this database. I have viewing access to the database and I can run queries to my hearts desire to get any info that I can divine from it.
Is that so odd? Many here have the same limited rights, why would you be distrustful of that?
My lack of rights was known to me obviously and why I never have created a table. I know I cannot, so I never have tried to mess with it! And I will NOT get admin rights, nor do I want them. I'm a dang pharmacist, not a DBA! I do this for fun b/c I'm a geek at heart and I also want to help my team.
By simply running queries and pasting into excel my final product, I have found wonderful data for my pharmacy director, manager, peers etc. Like drug usage data for specific timeframes so our buyer has a dang hint on what to order, since she has no report otherwise! EG:
with cte as
(
select eo.DrugID,[1dayRxUse],[3dayRxUse],[7dayRxUse],[14dayRxUse],[30dayRxUse] from
(select a.DrugID,count(*)as [1dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-2,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-2,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)a
group by a.DrugID
)ao
FULL JOIN
(select b.DrugID,count(*)as [3dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-4,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-4,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)b
group by b.DrugID
)bo
ON ao.DrugID = bo.DrugID
FULL JOIN
(select c.DrugID,count(*)as [7dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-8,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-8,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)c
group by c.DrugID
)co
ON bo.DrugID = co.DrugID
FULL JOIN
(select d.DrugID,count(*)as [14dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-15,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-15,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)d
group by d.DrugID
)do
ON co.DrugID = do.DrugID
FULL JOIN
(select e.DrugID,count(*)as [30dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-31,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-31,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)e
group by e.DrugID
)eo
ON do.DrugID = eo.DrugID
group by eo.DrugID,[1dayRxUse],[3dayRxUse],[7dayRxUse],[14dayRxUse],[30dayRxUse]
)
select (GenericName+ ' '+Strength+' '+DispenseFormID)as DrugDesc,d.TypeID,cte.DrugID
,CASE WHEN [1dayRxUse] IS NULL THEN '' ELSE [1dayRxUse] END AS [1dayRxUse]
,CASEWHEN [3dayRxUse] = [1dayRxUse] THEN ''
WHEN ([3dayRxUse]/[1dayRxUse]) < 2 THEN 'HighShortUse' else '' end as HighShortUse
,CASE WHEN [3dayRxUse] IS NULL THEN '' ELSE [3dayRxUse] END AS [3dayRxUse]
,CASEWHEN [7dayRxUse] = [3dayRxUse] THEN ''
WHEN ([7dayRxUse]/[3dayRxUse]) < 2 THEN 'HighMidUse' else '' end as HighMidUse
,CASE WHEN [7dayRxUse] IS NULL THEN '' ELSE [7dayRxUse] END AS [7dayRxUse]
,CASE WHEN [14dayRxUse] IS NULL THEN '' ELSE [14dayRxUse] END AS [14dayRxUse]
,CASE WHEN [30dayRxUse] IS NULL THEN '' ELSE [30dayRxUse] END AS [30dayRxUse]
from cte
INNER JOIN DPhaDrugData d on cte.DrugID = d.DrugID
group by d.GenericName,d.Strength,d.DispenseFormID,d.TypeID,cte.DrugID,[1dayRxUse],[3dayRxUse]
,[7dayRxUse],[14dayRxUse],[30dayRxUse]
having [30dayRxUse] > 29
order by [30dayRxUse] desc
I'm proud of that code, it took me a long time to figure out without asking questions here or to anyone, and it has helped us figure some things out. It just breaks down the drug-specific numbers based on date timeframes for utilization. But all I can do is run queries and use the "cte" function as my 'temporary table' if I have to, it seems like my best and only option.
At any rate! I just wanted a simple question answered. I've done what I've been asked to try to help to the best of my ability.
I am in the "Newbie" forum right? Can't someone please just try to help with MY question? If there were a lower place here to go ask questions I would, but I figured I'm appropriately at the bottom. I'm not trying to piss off all the SQL guru's around.
And it makes me mad I can't figure it out myself and I have to ask, but maybe I'm in the wrong place. I'm tryin hard and ain't feelin any love...
February 11, 2012 at 5:41 am
Please don’t get me wrong. It’s all database security breach stuff (mess) that I am handling nowadays & it’s making me mad.
I appreciate all your efforts that you put in but LIVE / PROD database is not the right place for R&D. Healthcare is as sensitive domain as Finance. You shouldn’t touch any data that you are not authorized to (not even try to do, even if you have rights by mistake). The code you shared here was not required IMHO.
Don’t want to discourage you. You can always practice SQL at your home. SQL Evaluation edition is free for 6 months for such usage. Developer edition is for $50 if you want to continue your study more than 6 months.
This forum is very polite & friendly. You can visit SSC as many times with your queries and guys here will help you.
I apologize if I used hard words.
February 11, 2012 at 6:06 am
pharmboy4u (2/11/2012)
Hello,Sorry I'm not posting in a table form, I don't create them so I'm not fluent in the lingo and it would take me forever (I am a SQL newbie!). I should be able to figure out this problem but I seem to have brain block. A simple example would be: I have selected out two columns with many rows. colA= text and colB= numbers like this:
colA, colB
aaa,2
aaa,3
aaa,3
bbb,0
bbb,1
bbb,1
Now I need to know aaa = 8 and bbb = 2, and repeat this count for each distinct colA value. It seems like a simple issue but dang it, it escapes me completely tonight. Any help would be great.
Thanks.
To answer your original question:
SELECT colA, SUM(colB) as cnt
FROM YourTable
February 11, 2012 at 8:30 pm
Lutz,
Thank you. However, when I tried this before, it counts the distinct numbers in colB as 1 group and does not add them to the others for the same colA entry. IE- when I "select colA,sum(colB)" on:
colA,colB
aaa,0
aaa,0
aaa,1
aaa,1
aaa,1
The result set I'm getting is:
colA,colB
aaa,0
aaa,3
For some reason, it sums all the 0's and puts that in one row, then all the 1's in another row, etc. FYI, colB is set up as (decimal,20,7) on this table, I don't know if that matters. There are also about 20 other columns that I don't care about and I'm only selecting two as in this example. I would think the other columns wouldn't mess up the 'sum' since I'm not selecting them, but I don't know.
Because of this I thought I was using the function wrong and had to find another way, since I'm not that adept. Perhaps something else is going on?
Any help is appreciated, thanks.
February 11, 2012 at 9:16 pm
Thanks for the test data setup code. It makes it a whole lot easier and timely for me to help.
Here's your test data and the solution. It returns precisely what you asked for you in your original post... aaa = 8 and bbb = 2.
drop table tab
go
create table tab(
colA varchar(7),
colB int)
go
insert into tab(colA,colB)
select 'aaa','2' union all
select 'aaa','3' union all
select 'aaa','3' union all
select 'bbb','0' union all
select 'bbb','1' union all
select 'bbb','1'
select * from tab
SELECT colA, Total = SUM(colB)
FROM tab
GROUP BY colA
ORDER BY colA
;
colA Total
------- -----------
aaa 8
bbb 2
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2012 at 10:05 pm
Thanks Jeff.
The problem is my test code must not adequately reflect the issue I'm having. My issue is still separating the 'sums' as I posted just prior to your post. I do not know what separates the simple test example I gave versus the table I'm dealing with, which is part of the problem.
*********************************************
I just had a breakthrough as I was doodling with this post and SQL. In the end of my problem code I had:
"group by colA,colB"
I just assumed "colB" had to be there and typed it w/o thinking. That must be why it separated colB on distinct value counts. I had no idea! I thought if you used 'sum' you had to put ALL referenced select columns in the grouping clause...
So when I put "group by colA" alone, it counts correctly now! Jeez...
Is this right?
February 11, 2012 at 10:14 pm
pharmboy4u (2/11/2012)
Thanks Jeff.The problem is my test code must not adequately reflect the issue I'm having. My issue is still separating the 'sums' as I posted just prior to your post. I do not know what separates the simple test example I gave versus the table I'm dealing with, which is part of the problem.
*********************************************
I just had a breakthrough as I was doodling with this post and SQL. In the end of my problem code I had:
"group by colA,colB"
I just assumed "colB" had to be there and typed it w/o thinking. That must be why it separated colB on distinct value counts. I had no idea! I thought if you used 'sum' you had to put ALL referenced select columns in the grouping clause...
So when I put "group by colA" alone, it counts correctly now! Jeez...
Is this right?
Yep... that's right. Only the columns that have no aggregates must be/should be listed in the GROUP BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2012 at 8:06 pm
Thanks for the help guys. I'm sorry it was so simple, and that simplicity took me so long to find.
Do I mark this thread as 'resolved' somehow?
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply