3 Replies Latest reply on Dec 1, 2011 10:50 AM by Catalysttgj

    Counting the total # of hard drives?

    Rookie

      Hi all.

       

      I am writing a custom report using SQL syntax, and all is going quite well with the exception of where I am trying to count the # of physical hard drives and return a total...

       

      There seems to be a built in floppy drive count, but for reasons beyond comprehension a physical hard disk count was left out.

       

      In any case, I can't seem to get the syntax right.

       

      Everything is done using SELECT DISTINCT and Unions so its quite difficult inserting some count commands..I must have tired 10 different methods for this but I am hitting a brick wall.

       

      Any insight would be very helpful!

       

      Here is the code:

       

      --Hard Drive Count
      SELECT DISTINCT
          Computer.Computer_Idn,
          Computer.DeviceName,
          5,    
          DriveNo,
          null,
          0,
          N'Total # of Hard Drives', 
          DriveNo,
          null
      FROM Computer LEFT OUTER JOIN FixedDrives ON Computer.Computer_Idn = FixedDrives.Computer_Idn
      UNION ALL
      
      --End Hard Drive Count
      

       

      I can't just toss in a Count (DriveNo) as it complains about Computer_IDN not being included.

      I can't seem to wrap the sql inside a nested select for counting either as it then complains about columsn not being given names, etc.

        • 1. Re: Counting the total # of hard drives?
          Catalysttgj Expert

          this code will count the fixed drives and provide a column: (Give it a shot.)

           

           

          SELECT DISTINCT
          c.DeviceName,  
          (select count(*) from FixedDrives as F1 where c.Computer_Idn = F1.Computer_Idn) as 'Fixed drive count'
          FROM Computer as c
          1 of 1 people found this helpful
          • 2. Re: Counting the total # of hard drives?
            Rookie

            Thanks, that was a great step for me in the right direction.

            However it still does not seem to work the way I think I am trying to get it to work.

             

             

            UNION ALL
             SELECT DISTINCT 
                 Computer.Computer_Idn,
                 Computer.DeviceName,
                 0,
                 null,
                 null,
                 0,
             (select count(*) from FixedDrives as F1 where Computer.Computer_Idn = F1.Computer_Idn) as 'Fixed drive count',
             null,
             null
             FROM Computer LEFT OUTER JOIN FixedDrives ON Computer.Computer_Idn = FixedDrives.Computer_Idn    
             UNION ALL
            

             

            This is what I  have as it has to follow this type of pattern.

             

            I'm  using the base report of HArdware Inventory from LDMS 9.2 in this case  and modifying it, so I am keeping in line with the existing SQL code for  column order and display, if tha thelps.

             

            I'm now getting: 'Conversion failed when converting the nvarchar value 'BIOS - Copyright String' to data type int.'

             

            The first part of the query the report uses is:

             

            SELECT distinct Table1.* FROM
             (
            SELECT DISTINCT
                 Computer.Computer_Idn,
                 Computer.DeviceName,
                 0 Section,
                 null GroupInt,
                 null GroupStr,
                 0 DetailNo,
                 N'BIOS - Copyright String' DetailLabel,
                 null IntValue,
                 COPYRIGHT StrValue
             FROM Computer LEFT OUTER JOIN BIOS ON Computer.Computer_Idn = BIOS.Computer_Idn
             UNION ALL
            )
            
            

            Seems I'm either overlooking something or the new code is pretty much on par with the layout.

            • 3. Re: Counting the total # of hard drives?
              Catalysttgj Expert

              The issue is that each sql code chunk needs to have the exact same column set. They have to be in the exact same positions, and they have to be the same cast. So you may need to recast certain data from different places. If it were me i'd probably recast everything to varchar so it's simpler that way, but your call on that one.

               

              If you need some more help with this, i can look at it later this evening. If you haven't already please post the whole code so i can try it out on my end, even if you fix it. I'm curious about what all you're doing with it.

               

              thanks.