5 Replies Latest reply: Dec 23, 2013 6:04 AM by Partha Sarathy S RSS

    Query combining two tables

    Saumyadip Sarkar

      I have the following two tables:

       

      Asset_Issued

      Asset_Type_IDAssigned To
      1Ram
      2Ram
      3Ram
      4Ram
      5Ram
      1Raju
      3Raju
      5Raju
      1Rajesh
      3Rajesh
      2Rajesh

       

       

      Asset_Type

      Asset_Type_IDAsset_Name
      1Laptop
      2Desktop
      3Mouse
      4Keyboard
      5Monitor

       

       

      Can anyone help me with the query that will give the following result.

       

      Asset_Issued_Count

      Asset_NameCount
      Laptop3
      Desktop2
      Mouse3
      Keyboard1
      Monitor1
        • 1. Re: Query combining two tables
          kendenny

          Really? This is really basic stuff.

           

          select at.asset_name,count(*)

            from asset_type at

            join asset_issued ai on (ai.asset_type_id = at.asset_type_id)

            group by at.asset_name;

          • 2. Re: Query combining two tables
            AnnPricks E

            Try the below

             

            SELECT asset_name,COUNT(1) FROM Asset_Issued ai,
            Asset_Type at

            WHERE ai.
            Asset_Type_ID = at.Asset_Type_ID

            GROUP BY asset_name;

            • 3. Re: Query combining two tables
              Hoek

              select asset_name

              ,      count(*)

              from   asset_issued a

              ,      asset_type t

              where  t.asset_type_id = a.asset_type_id

              group by asset_name;

              You may want to use ORDER BY asset_name as well...

              • 4. Re: Query combining two tables
                vijayrsehgal-Oracle
                select ast.asset_name, count(*) from asset_type ast, assest_issued ai
                were ast.Asset_Type_ID = ai.Asset_Type_ID
                group by ast.asset_name;
                
                • 5. Re: Query combining two tables
                  Partha Sarathy S

                  I think your OUTPUT COUNT for MONITOR should be 2 and not 1. Try the below.

                   

                  WITH ASSET_ISSED(ASSET_TYPE_ID,ASSIGNED_TO) AS(

                  SELECT 1,'Ram' FROM DUAL UNION ALL

                  SELECT 2,'Ram' FROM DUAL UNION ALL

                  SELECT 3,'Ram' FROM DUAL UNION ALL

                  SELECT 4,'Ram' FROM DUAL UNION ALL

                  SELECT 5,'Ram' FROM DUAL UNION ALL

                  SELECT 1,'Raju' FROM DUAL UNION ALL

                  SELECT 3,'Raju' FROM DUAL UNION ALL

                  SELECT 5,'Raju' FROM DUAL UNION ALL

                  SELECT 1,'Rajesh' FROM DUAL UNION ALL

                  SELECT 3,'Rajesh' FROM DUAL UNION ALL

                  SELECT 2,'Rajesh' FROM DUAL),

                  ASSET_TYPE(ASSET_TYPE_ID,ASSET_NAME) AS

                  (SELECT 1,'Laptop' FROM DUAL UNION ALL

                  SELECT 2,'Desktop' FROM DUAL UNION ALL

                  SELECT 3,'Mouse' FROM DUAL UNION ALL

                  SELECT 4,'Keyboard' FROM DUAL UNION ALL

                  SELECT 5,'Monitor' FROM DUAL)

                  SELECT ASSET_NAME,

                         COUNT(1)

                  FROM

                  ASSET_ISSED T1,

                  ASSET_TYPE T2

                  WHERE T1.ASSET_TYPE_ID=T2.ASSET_TYPE_ID

                  GROUP BY T2.ASSET_NAME;

                   

                  OUTPUT:

                  ASSET_NA   COUNT(1)

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

                  Desktop           2

                  Laptop            3

                  Mouse             3

                  Keyboard          1

                  Monitor           2