CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Watson
Watson

Reputation: 33

Merge row values from separate tables into one column. [SQL]

Hi I am just learning SQL and i am trying to retrieve some data from multiple tables and cannot get it to display how I would like it, so i am just wondering if this is possible or not, and if so could someone suggest a method, see below:

    Table1:                     Table2:                     Table3

    Package       Licenses      Package       Licenses      Package      Licenses
    ======================      ======================      =====================
    Product1         20         Product1         15         Product1        0
    Product2         15         Product2         5          Product2        5
    Product3         10         Product3         10         Product3        0

I would like the values in the Licenses columns to be added to each other according to the package name and it to display something like this if possible:

    Package       Licenses
    ======================
    Product1         35
    Product2         25
    Product3         20

If someone could let me know how this could be done, if such a thing is even possible please let me know.

Upvotes: 3

Views: 476

Answers (3)

Yaroslav
Yaroslav

Reputation: 6534

Try this:

SELECT Package, SUM(Licences) as TotalLicences
  FROM (
        SELECT Package, Licences FROM Table1
         UNION ALL
        SELECT Package, Licences FROM Table2
         UNION ALL
        SELECT Package, Licences FROM Table3
       ) AS AllLicences
 GROUP BY Package

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You could use a union to create a view of all licences, and sum them:

select  Package
,       sum(Licences) as Licences
from    (
        select  Package
        ,       Licences
        from    Table1
        union all
        select  Package
        ,       Licences
        from    Table2
        union all
        select  Package
        ,       Licences
        from    Table3
        ) as SubQueryAlias
group by
        Package

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

It's the UNION ALL clause and GROUP BY statement you need researching.

The gist of the solution is to

  • combine all rows from all tables using a UNION ALL
  • put the results into a subselect
  • calculate the total licenses by using a GROUP BY and the SUM aggregated function over the results of the subselect

SQL Statement

SELECT Package, SUM(Licenses) AS Licenses
FROM   (
         SELECT Package, Licenses FROM Table1 UNION ALL
         SELECT Package, Licenses FROM Table2 UNION ALL
         SELECT Package, Licenses FROM Table3
       ) t
GROUP BY
       Package

Upvotes: 2

Related Questions