CODEWITHSUNDEEP
mysqlsql-serversum
GhislainJC
GhislainJC

Reputation: 3

Transition from mySQL to msSQL Sum Query wont work

I've been reading multiple forums and looked everywhere for the answer but I can't seem to find someone who had a similar problem to what I've encountered. we are currently migrating our database from mySQL to msSQL. i have a query in mySQL that work very well for what i needed it to do but i can't seem to figure it out in msSQL. what i am trying to do is show how many computers are due to be replaced on a certain year, how many are not eligible, how many are in progress and how many were completed while keeping the initial total. here is what i had in mySQL

SELECT RefreshCycle, 
       Count(*) - sum(RefreshStatus = 'Not Eligible') as Total,
       sum(RefreshStatus = 'Completed') as 'Completed', 
       sum(RefreshStatus = 'Not Started') as 'Not Started', 
       sum(RefreshStatus = 'In Progress') as 'In Progress', 
       sum(RefreshStatus = 'Not Eligible') as 'Not Eligible' 
FROM [InventoryDatabase].[dbo].[Hardware_Inventory] 
group by RefreshCycle, RefreshStatus 
order by RefreshCycle asc;

which gave me a table result

enter image description here

currently I have columns named RefreshCycle and RefreshStatus for each computer entry in the database and I need to be able to do this reports, I'm stumped on this, I dont know why SQL is telling me i have a incorrect syntax for my sums. any help would be appreciated

Upvotes: 0

Views: 46

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT RefreshCycle, 
       Count(*) - sum(CASE WHEN RefreshStatus = 'Not Eligible' THEN 1 ELSE 0 END) as Total,
       sum(CASE WHEN RefreshStatus = 'Completed' THEN 1 ELSE 0 END) as 'Completed', 
       sum(CASE WHEN RefreshStatus = 'Not Started' THEN 1 ELSE 0 END) as 'Not Started', 
       sum(CASE WHEN RefreshStatus = 'In Progress' THEN 1 ELSE 0 END) as 'In Progress', 
       sum(CASE WHEN RefreshStatus = 'Not Eligible' THEN 1 ELSE 0 END) as 'Not Eligible' 
FROM [InventoryDatabase].[dbo].[Hardware_Inventory] 
group by RefreshCycle 
order by RefreshCycle;

In SQL Server 2012+ this can be simplified to:

SELECT RefreshCycle, 
       Count(*) - sum(IIF(RefreshStatus = 'Not Eligible', 1, 0)) as Total,
       sum(IIF(RefreshStatus = 'Completed', 1, 0)) as 'Completed', 
       sum(IIF(RefreshStatus = 'Not Started', 1, 0)) as 'Not Started', 
       sum(IIF(RefreshStatus = 'In Progress', 1, 0)) as 'In Progress', 
       sum(IIF(RefreshStatus = 'Not Eligible', 1, 0)) as 'Not Eligible' 
FROM [InventoryDatabase].[dbo].[Hardware_Inventory] 
group by RefreshCycle 
order by RefreshCycle;

Upvotes: 2

Related Questions