CODEWITHSUNDEEP
sql
Justin Loveless
Justin Loveless

Reputation: 522

Calculating totals in SQL

I have two tables, Table a and Table b.

Table a is set up like...

Users

Id  ...  InspectStat    PurchaseStat    SoldStat    POSStat
------------------------------------------------------------
 1        
 2
 3

Properties

UserId  ...  Stage  
---------------------------------------------------    
    1        Inspect 
    3        Purchase
    1        Sold
    2        POS

I am trying to write a Job that will every hour, calculate the percentage of each users total properties in each stage. so for example, the end result should look like this (assuming that there are only these 4 properties)

Id  ...  InspectStat    PurchaseStat    SoldStat    POSStat
------------------------------------------------------------
 1           .5               0             .5         0
 2            0               0              0         1.0
 3            0               1.0            0         0

I have tried 

ALTER PROCEDURE [dbo].[StatCounter]

AS
DECLARE @PropertyCount as float 

BEGIN

set @PropertyCount = (select count(AcquisitionAssociateId) from [dbo].[Properties],     [dbo].[Users]
WHERE
    [dbo].[Users].Id = [dbo].[Properties].AcquisitionAssociateId) 
update [dbo].[Users]
set [dbo].[Users].PurchaseStat = ((select count (AcquisitionAssociateId) from [dbo].[Properties]
WHERE 
    [dbo].[Users].Id = [dbo].[Properties].AcquisitionAssociateId 
    and 
    [dbo].[Properties].Stage = 'Purchase') / @PropertyCount)

...Repeat for 3 other stages

This method is correctly counting the number of properties in each stage, but after executing it, I realized that what it is actually doing is counting the number of properties that have a user assigned to them, and not getting a distinct count of how many properties an individual user actually has. I'm learning SQL as I go, so help would be much appreciated.

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a special case of a pivot. The following does the calculation using avg(). It assigns the value of "1" for the stat and the average does the division by the total on the row:

select userid,
       avg(case when Stage = 'Inspect' then 1.0 else 0.0 end) as InspectStat,
       avg(case when Stage = 'Purchase' then 1.0 else 0.0 end) as PurchaseStat,
       avg(case when Stage = 'Sold' then 1.0 else 0.0 end) as SoldStat,
       avg(case when Stage = 'POS' then 1.0 else 0.0 end) as POSStat
from properties p
group by userid

Upvotes: 1

Related Questions