SQL Query to Update Object Count Based on Event Name

SQL Query to Update Object Count Based on Event Name

Imagine I am an owner of many bookstores. I keep a database of all events that occur in all of my many bookstores. Two events of note are "Book Added" and "Book Removed", for when a book is added to the inventory of a story, and when it is sold from a store. An example schema would be bookstore_id, event_name, `time.

Now say I have a second table, which maintains the current state of each bookstore, so the schema would be bookstore_id, num_books.

I want to be able to use the first table to get the count of all the "Book Added" events per bookstore, subtract the count of all the "Book Removed" events per bookstore, and then update the number of books in each bookstore in the second table.

The only way I can think to do it requires using a cursor, but I'm assuming there's a more "SQL-esque" way to do it that is more set-based and doesn't require a cursor.

Answer by Sravan Kumar for SQL Query to Update Object Count Based on Event Name

We can use CTEs to get details individually and process them.

With CTE_Add AS  (  Select Bkstr_ID, Count(event_Name) As Added From temp Where event = 'Added' Group by Bkstr_ID  ), CTE_Rem As  (  Select Bkstr_ID, Count(event_Name) As Removed From temp Where event = 'Removed'      Group by Bkstr_ID  )  Select A.Bkstr_ID,  Added - Removed  From CTE_Add A  Left Join CTE_Rem R On A.Bkstr_ID= R.Bkstr_ID  

This will give you ID and count. Instead of select, you can use Insert statement

Answer by Frisbee for SQL Query to Update Object Count Based on Event Name

select bookstore_id       , sum(case when event_name = "Book Removed" then -1 else 1 end) as "num books"    from bookstores     group by bookstore_id   

if more than 2 events

select bookstore_id       , sum(case when event_name = "Book Removed" then -1                   when event_name = "Book Added"   then  1               end) as "num books"    from bookstores     group by bookstore_id   

And I would just make it a view unless you come up with performance issues

Answer by Decoded for SQL Query to Update Object Count Based on Event Name

I'd use SUM(CASE WHEN ...). Below is an example.

If object_id('tempdb..#BoookStores') Is Not Null Drop Table #BoookStores  Create Table #BoookStores (bookstore_id int, num_books int)    /* We have 3 stores */  Insert #BoookStores (bookstore_id, num_books)  Values  (1, 0), (2, 0), (3, 0)    If object_id('tempdb..#Events') Is Not Null Drop Table #Events  Create Table #Events (bookstore_id int, event_name varchar(10), time dateTime Default(GetDate()) )    Insert #Events (bookstore_id, event_name)  Values  (1, 'Added'), (1, 'Added'), (1, 'Added'), (1, 'Added'), -- Added 4 books to 1. store  (2, 'Added'), (2, 'Added'), (2, 'Added'), -- Added 3 books to 2. store  (3, 'Added'), (3, 'Added'), -- Added 2 books to 3. store    /* removed 2 books from each stores */  (1, 'Removed'), (1, 'Removed'),   (2, 'Removed'), (2, 'Removed'),  (3, 'Removed'), (3, 'Removed')    /* Calculate adds and removes. Update the results */  ;With Tmp As (      Select  E.bookstore_id,              Sum(Case When E.event_name = 'Added' Then 1 Else 0 End) As AddCount,              Sum(Case When E.event_name = 'Removed' Then 1 Else 0 End) As RemoveCount      From    #Events E      Group By E.bookstore_id  )  Update  BS Set num_books = T.AddCount-T.RemoveCount  From    #BoookStores BS          Inner Join Tmp T On T.bookstore_id = BS.bookstore_id     /* check results*/  Select * From #BoookStores BS  

Answer by JNevill for SQL Query to Update Object Count Based on Event Name

Something like this will get you in the ball park. Similar logic could be used for INSERT.

UPDATE tableA  SET tableA.num_books = tableB.num_books  FROM secondTable AS TableA  INNER JOIN (      SELECT bookstore_id,          SUM(CASE                   WHEN event_name = 'Books Added'                      THEN 1                  END) - SUM(CASE                   WHEN event_name = 'Books Removed'                      THEN 1                  END              ) AS num_books      FROM firstTable      GROUP BY bookstore_id      ) TableB ON TableA.bookstore_id = tableB.bookstore_id  

Answer by DhruvJoshi for SQL Query to Update Object Count Based on Event Name

You can try a query like below:

update t1  set num_books=inventory  FROM bs t1 LEFT JOIN  (select bookstore_id,SUM(case when event_name like 'A' then 1 when event_name like 'R' then -1 else NULL end)  as inventory  from bse   group by bookstore_id) t2  on t1.bookstore_id=t2.bookstore_id  

Live SQL demo

Answer by Ric .Net for SQL Query to Update Object Count Based on Event Name

You can count the events by using a GROUP BY clause.

If we would create 2 subtables where we count the added respectively the removed books, we can simply subtract the results and update these in the parent table. This will look like:

UPDATE      b  SET         b.numbooks = AddedBooks.BooksAdded - RemovedBooks.BooksRemoved  FROM        dbo.Books b  INNER JOIN  (SELECT     be.book_id, count(*) AS BooksAdded              FROM        dbo.BookEvents be              WHERE       be.event = 'BookAdded'              GROUP BY    be.book_id, be.event) AS AddedBooks                                       ON b.bookid = AddedBooks.book_id  INNER JOIN  (SELECT     be.book_id, count(*) AS BooksRemoved              FROM        dbo.BookEvents be              WHERE       be.event = 'BookRemoved'              GROUP BY    be.book_id, be.event) AS RemovedBooks                                        ON b.bookid = RemovedBooks.book_id  

Answer by JamieD77 for SQL Query to Update Object Count Based on Event Name

UPDATE  bsc  SET     bsc.num_books = bse.num_books  FROM    bookstorecounts bsc          JOIN (SELECT    bookstore_id,                          SUM(CASE event_name                                WHEN 'Book Removed' THEN -1                                WHEN 'Book Added' THEN 1                              END) AS num_books                FROM      bookstoreevents                GROUP BY  bookstore_id          ) bse ON bsc.bookstore_id = bse.bookstore_id  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72