Updating a numeric(int) column with simple math [Solved]

Transact-SQL
1

Hello

So here is my issue.. I have three tables.

One table is for user information (USERS)
One table is for the content they add to the website (SITES)
The last table is for the photos they have uploaded (GALLERYINDEX)

So let's say you are "Joe" in the USERS table. Hi Joe! Joe creates an entry in the SITES table for each place he's visited with a camera to take photos.

Then GALLERYINDEX holds the number of photo galleries he's posted entirely. It's possible Joe has more than one row here belonging to one or more SITES. Say Joe visited Toronto and posted 1 gallery, then Galleryindex has 1 row under Toronto. Joe could theoretically have three rows if he has 3 galleries from Toronto.

Currently on a user's profile I display the # of galleries this way:

SELECT COUNT(*) as ID1 from galleryindex where gcreator = 'Joe'
So ID1 will show 10 if Joe has added 10 photo galleries to "GALLERYINDEX". (10 galleries in total from all possible sites he's been to)

Then I get a count of the number of sites Joe has been to that he's taken photos of.

SELECT COUNT(id) as ID2 from sites where creator = '"Joe"

What I'd like to do is to update the USERS table so Joe's (and ALL users) are updated with their engagement in a column (integer) named STATS.

ID1 will be divided by 4 and ID2 divided by 2 and the result goes into dbo.users.stats

So to summarize:

A) Table: galleryindex column: gcreator
B) Table: sites column: creator

  1. Get total rows from A and B where Joe is in that column
  2. Take A and divie by 4
  3. Take B and divide by 2
  4. Take total of 2 and 3 above and put it into USERS.STATS

Example:
Joe has 13 sites he's been to with a camera, and 14 galleries (one site has 2 galleries)
Take 13 and divide by 2, 14 divide by 4 and put total into Joe's "stats" column under USERS

But this should occur on all users not just Joe's

I hope this isn't too confusing :slight_smile:

2

Not sure of what the user column name to match to [g]creator is, but something like this:

UPDATE U
SET STATS = ISNULL(GI.ID1 / 4, 0) + ISNULL(S.ID2 / 2, 0)
FROM dbo.USERS U
LEFT OUTER JOIN (
    SELECT gcreator, COUNT(*) AS ID1 
    FROM dbo.galleryindex 
    GROUP BY gcreator
) AS GI ON GI.gcreator = U.[user]
LEFT OUTER JOIN (
    SELECT creator, COUNT(id) AS ID2
    FROM dbo.sites
    GROUP BY creator
) AS S ON S.creator = U.[user]
1 Like
3

It worked perfectly. Thank you so much, brilliant!