title: Simple math in T-SQL
body: <p>I am using a Scalar-Valued Function to find out if a user of the system has images, audio files or videos in their profile.</p>
  
  <p>When I search through members I want to order the members by amount of media they have.  So for example a user with all media (videos, audio and images) would get 3, 1 point for each media they have.  If they have 2 media(images and video) but no audio they would get 2.  This would be listed after all the users that have all 3 media.</p>
  
  <p>The stored procedure has this:</p>
  
  <pre><code>SELECT
  a.MemberID,
  a.UserName,
  a.LastLogin,
  a.City,
  b.Abbr,
  c.Country,
  a.AvatarMed,
  a.Gender,
  sbuser.sf_MemberHasImages(a.MemberID),
  sbuser.sf_MemberHasVideo(a.MemberID),
  sbuser.sf_MemberHasAudio(a.MemberID),
  d.Domain,
  sbuser.sf_DisplayName(a.MemberID),
  a.CreateDate,
  a.Claimed,
  a.ProfileTypeID,
  a.Zip,
  a.PhoneNbr,
  a.PrPhone
  FROM Member a
  LEFT JOIN State b ON b.StateID = a.StateID
  INNER JOIN Country c ON c.countryID = a.CountryID
  INNER JOIN Region d ON d.RegionID = a.MemberREgionID
  WHERE ProfileTypeID IS NOT NULL
  AND (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%')
  AND a.MemberID &lt;&gt; @MemberID
  ORDER BY a.Claimed DESC, a.AvatarTiny DESC, sbuser.sf_MemberHasMedia(a.MemberID)
  </code></pre>
  
  <p>If you note the order by, it contains the following:</p>
  
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [sbuser].[sf_MemberHasMedia](@MemberID bigint)
  
  RETURNS BIT
  AS
  BEGIN
  DECLARE @OUTSTR INT
  DECLARE @OUT1 BIT
  DECLARE @OUT2 BIT
  DECLARE @OUT3 BIT
  SET @OUT1 = (SELECT CAST(COUNT(MemberAudioID) AS BIT) FROM MemberAudio
  WHERE MemberID @MemberID)
  RETURN @OUTSTR = @OUT1
  END
  
  RETURNS BIT
  AS
  BEGIN
  SET @OUT2 = (SELECT CAST(COUNT(a.MemberImgID) AS BIT)
  From MemberImg a
  INNER JOIN MemberImgGallery b ON b.MemberImgGalleryID=a.MemberImgGalleryID
  WHERE b.MemberID = @MemberID)
  RETURN @OUTSTR = @OUTSTR + @OUT2
  END
  
  RETURNS BIT
  AS
  BEGIN
  SET @OUT3 = (SELECT CAST(COUNT(MemberVideoID) AS BIT) FROM MemberVideo
  WHERE MemberID = @MemberID)
  RETURN @OUTSTR = @OUTSTR + @OUT3
  END
  </code></pre>
  
  <p>I am lost in the logic of this, and it should be easy.  Any help would be greatly appreciated.</p>
  
  <p>Many thanks,
  Paul</p>