CODEWITHSUNDEEP
sqlsql-serversql-server-2008
coburne
coburne

Reputation: 149

SQL UPDATE: integer divided by Count Distinct values

This is an addendum to this question I asked regarding dividing an integer by the count of a specified value: SQL UPDATE: integer divided by count of a specified value

I'm trying to adjust that to divide an integer based on the count of distinct values.

Example: $10,000 of shipping costs for each distinct occurrence in Shipping_State will be divided equally amongst the number of records in that distinct occurrence. That value will go in the Column 'Cost'.

So for the 5 records that say Ohio, cost is 2000.0000 each, 10 records for Texas will return 1000.0000 each, 42 records for California, 238.0952 each, etc.

I made a few efforts in vain, here's one attempt:

UPDATE CustomerTable
SET Cost = (SELECT 10000.00 / count(*)
            FROM CustomerTable
            WHERE CustomerTable.Shipping_state = COUNT(DISTINCT CustomerTable.Shipping_State)
WHERE CustomerTable.Shipping_State = COUNT(DISTINCT CustomerTable.Shipping_State;

I get this error:

Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Upvotes: 0

Views: 1585

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I assume this is what you are looking for:

UPDATE CustomerTable
    SET Cost = (SELECT 10000.00 / count(*)
                FROM CustomerTable ct2
                WHERE CustomerTable.Shipping_state = ct2.Shipping_State
               );

I think you are better off with my solution to your previous question, but without the where:

with toupdate as (
      select ct.*, count(*) over (partition by ct.Shipping_State, month(ct.Shipping_Date)) as cnt
      from CustomerTable
     )
update toupdate
    set Cost = cast(10000 as float) / cnt;

Edit:

If you don't want the date, just remove it from the partition by clause:

with toupdate as (
      select ct.*, count(*) over (partition by ct.Shipping_State) as cnt
      from CustomerTable
     )
update toupdate
    set Cost = cast(10000 as float) / cnt;

Upvotes: 1

Related Questions