我正在编写一个查询,以生成药物及其平均价格的列表。
我的桌子看起来像:
CREATE TABLE [dbo].[drugPurchases](
[importId] [bigint] IDENTITY(1,1) NOT NULL,
[importDate] [datetime] NOT NULL,
[rxNumber] [float] NULL,
[accountNumber] [nvarchar](255) NULL,
[lastName] [nvarchar](255) NULL,
[firstName] [nvarchar](255) NULL,
[dob] [datetime] NULL,
[ssn] [nvarchar](10) NULL,
[drugName] [nvarchar](255) NULL,
[drugStrength] [nvarchar](255) NULL,
[dosage] [nvarchar](255) NULL,
[quantityDispensed] [float] NULL,
[price] [float] NULL,
[facilityCode] [nvarchar](255) NULL,
[fillDate] [datetime] NULL,
[processed] [bit] NOT NULL
一种名为"ENLAPRIL"的特定药物的一些样本数据。
select
drugName,
drugStrength,
dosage,
quantityDispensed,
price
from
drugPurchases
where
drugName = 'ENALAPRIL'
and drugStrength = '10MG'
and fillDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
结果:
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 180 3.42
ENALAPRIL 10MG TAB 120 2.28
ENALAPRIL 10MG TAB 240 31.18
ENALAPRIL 10MG TAB 300 38.97
ENALAPRIL 10MG TAB 240 31.18
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 120 15.59
ENALAPRIL 10MG TAB 120 15.59
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 1.14
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 60 7.79
ENALAPRIL 10MG TAB 180 3.42
我的目标是得到每种力量的平均价格。我的问题是:
SELECT
distinct(drugName),
drugStrength,
dosage,
ROUND(price / quantityDispensed, 2) as 'calc'
FROM
drugPurchases
where
fillDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
and drugName = 'ENALAPRIL'
and drugStrength = '10MG'
GROUP BY
drugName,
drugStrength,
dosage,
(price / quantityDispensed)
ORDER BY
drugName,
drugStrength
但我的结果集是ENLAPRIL 10MG:产生了两个不同的价格
ENALAPRIL 10MG TAB 0.02
ENALAPRIL 10MG TAB 0.13
知道它为什么要这样做,以及我可能需要如何调整我的查询吗?
看起来它在药品购买中按两个单独的条目分组,因此在您的组中按…
(price / quantityDispensed)
您可以使用SUM(价格/数量分配)聚合这两个值,并通过.删除组
SELECT
drugName,
drugStrength,
dosage,
ROUND(
Avg(price / quantityDispensed), 2) as 'calc'
FROM
drugPurchases
where
fillDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
and drugName = 'ENALAPRIL'
and drugStrength = '10MG'
GROUP BY
drugName,
drugStrength,
dosage,
ORDER BY
drugName,
drugStrength
您是按价格/数量分组的,因此每个价格/数量结果都会产生一个单独的返回行。在价格/数量中使用聚合,并通过将其从组中删除。我希望这有相同的结果:
SELECT
distinct(drugName),
drugStrength,
dosage,
ROUND(avg(price / quantityDispensed), 2) as 'calc'
FROM
drugPurchases
where
fillDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
and drugName = 'ENALAPRIL'
and drugStrength = '10MG'
GROUP BY
drugName,
drugStrength,
dosage
ORDER BY
drugName,
drugStrength