union operation with sum

Atherr Alam 1 Reputation point Microsoft Employee
2021-10-20T20:59:26.02+00:00

I am looking for a way to combine two tables with the same fields in a way that the devicename which is common between the two gets added
See below example

2 tables a and b, with identical columns, I want to union them, so one column is device name other is error count, if I use union, will the count column be summed itself, or we have to do something else, i.e.

T1
devicename error count
a 10
b 12
c 15

T2
devicename error count
b 77
c 88
d 99
e 15
f 19

union T1, T2

Current result
a 10
b 12
c 15
b 77
c 88
d 99
e 15
f 19

Intended result

a 10
b 89
c 103
d 99
e 15
f 19

Please let me know if I need to use some other method for the intended results.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
503 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 84,531 Reputation points Microsoft Employee
    2021-10-21T07:40:52.333+00:00

    Hello @Atherr Alam ,

    Welcome to the Microsoft Q&A platform.

    You may use the below query to get the intended result as per your requirement. 

     T1 | union T2  
| summarize sum(Value) by ID  
| order by ID asc

    142391-image.png

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators