CODEWITHSUNDEEP
sqlpostgresql
coco minion
coco minion

Reputation: 123

How to multiply in the same column sql

I have a table three columns Locations, Count Location, Table from. How can I multiply (Count Location) and then divide it to get the percentage.

Example: Trying to Divide both location that are in the same Column! and then Multiple by 100 (Count Location / Count Location)*100

    Atlanta, GA 6   CL Table
    Atlanta, GA 20  TW Table
    The result  (6/20)*100=30%  


|Location     |Count Location|  Table From|
-------------------------------------------
  Atlanta, GA   6                CL Table
  Atlanta, GA   20               TW Table
  Austin, TX    27               TW Table
  Austin, TX    5                CL Table
  Chicago, IL   6                CL Table
  Chicago, IL   19               TW Table
  Corona, CA    6                CL Table
  Corona, CA    50               TW Table
  Dallas, TX    37               TW Table
  Dallas, TX    3                CL Table
  Denver, CO    3                CL Table
  Denver, CO    19               TW Table
  Houston, TX   21               TW Table
  Houston, TX   11               CL Table

Is there are way to do this for all locations and show the results in a new column next to them? Or do I need to make a new table to make it easier?

Upvotes: 0

Views: 423

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

Okay, now I see. You want the CL percentage of TW. You can do this with window functions:

select t.*,
       (100 * max(case when tablefrom = 'CL Table' then count end) over (partition by location)/
        max(case when tablefrom = 'TW table' then count end) over (partition by location)
       ) as percentage
from table t;

If you just want the result per location (which makes more sense to me) just use aggregation:

select location,
       (100 * max(case when tablefrom = 'CL Table' then count end) /
        max(case when tablefrom = 'TW table' then count end)
       ) as percentage
from table t
group by location

Upvotes: 1

Related Questions