CODEWITHSUNDEEP
sqlazure-sql-database
Joseph Anderson
Joseph Anderson

Reputation: 4144

SQL Group Twice, Distinct Row

I am using SQL Azure. I am trying to group a set of results twice, so that the user name field is unique. In my results, the same rows appear several times with a different value for transaction count.

Here is my code:

select settings.UserName, SUM(TransactionCount) as TransactionCount, settings.SettingValue as    Sku, CASE 
WHEN settings.SettingValue = 'cccmicro' AND TransactionCount > 1800 THEN 'True'
WHEN settings.SettingValue = 'cccsub1' AND TransactionCount > 6000 THEN 'True'
WHEN settings.SettingValue = 'cccsub2' AND TransactionCount > 18000 THEN 'True'
WHEN settings.SettingValue = 'cccsub3' AND TransactionCount > 30000 THEN 'True' 
ELSE 'False'
END As [Over]
from jma_qb_trans_count trans
Inner Join jma_settings settings on settings.UserName = trans.UserName
where settings.SettingName = 'QB:JMAWebStoreSku' and settings.SettingValue != ''
Group by settings.UserName, settings.SettingValue, settings.SettingName, TransactionCount
Order By settings.UserName

Here is my data:

enter image description here

Here is what I'm seeking:

UserName: james.dick TransactionCount: 8 Sku: cloudcartconnectorlicense Over: False

Upvotes: 0

Views: 84

Answers (2)

user17130
user17130

Reputation: 261

Your group by is splitting the results put them in the select part and you will see why query does not work correctly.

Group by settings.UserName, settings.SettingValue, settings.SettingName, TransactionCount

Upvotes: 0

smzsmzsmz
smzsmzsmz

Reputation: 44

Please try the code below. I think it will work like you asked.

select 

settings.UserName, 
CASE 
WHEN settings.SettingValue = 'cccmicro' AND TransactionCount > 1800 THEN 'True'
WHEN settings.SettingValue = 'cccsub1' AND TransactionCount > 6000 THEN 'True'
WHEN settings.SettingValue = 'cccsub2' AND TransactionCount > 18000 THEN 'True'
WHEN settings.SettingValue = 'cccsub3' AND TransactionCount > 30000 THEN 'True' 
ELSE 'False'
END As [Over],
SUM(TransactionCount) as TransactionCount, 
settings.SettingValue as Sku

from jma_qb_trans_count trans
Inner Join jma_settings settings on settings.UserName = trans.UserName
where settings.SettingName = 'QB:JMAWebStoreSku' and settings.SettingValue != ''
Group by 1,2

Upvotes: 1

Related Questions