Summing a total from a select statmement that need distinct in it RSS

• • Reply
  

  TgaLamx

  Member

  25 Points

  61 Posts

  Summing a total from a select statmement that need distinct in it

  Feb 20, 2013 12:20 AM|LINK

  Anyone know a way to do this ?

   

  this is what I tried but my knowledge in sql programming is limited,

  thanks

  glen

   

  ALTER PROCEDURE dbo.getcompdate
  @custid int, @date1 date, @date2 date
  
  	/*
  	(
  	@parameter1 int = 5,
  	@parameter2 datatype OUTPUT
  	)
  	*/
  AS
  	/* SET NOCOUNT ON */
  	DECLARE @comp varchar(50)
  	SET @comp = (SELECT Company FROM Customer WHERE (ID = @custid))
  
  	 
  	
  	SELECT DISTINCT
  	                         Benchsurf.datequoted, Benchsurf.Jobnum, products.Product, Benchsurf.Brand, Benchsurf.colourshade, Benchsurf.Finish, Customer.username, ISNULL(SUM(ISNULL(Benchsurf.PriceMark,0)),0) AS TOTAL,
  	                         Benchsurf.PriceMark
  	FROM            Benchsurf INNER JOIN
  	                         products ON Benchsurf.Productid = products.ID INNER JOIN
  	                         Customer ON Benchsurf.custid = Customer.ID
  	WHERE    Benchsurf.datequoted >= @date1 AND Benchsurf.datequoted <= @date2 AND        (Customer.Company = @comp)
  
  
  	RETURN

• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Summing a total from a select statmement that need distinct in it

  Feb 20, 2013 01:03 AM|LINK

  I do not know if I understand correctly but try this:

  SELECT
      Benchsurf.datequoted,
      Benchsurf.Jobnum,
      products.Product,
      Benchsurf.Brand,
      Benchsurf.colourshade,
      Benchsurf.Finish,
      Customer.username,
      ISNULL(SUM(ISNULL(Benchsurf.PriceMark, 0)) OVER(), 0) AS TOTAL,
      Benchsurf.PriceMark
  FROM Benchsurf
  INNER JOIN products
      ON Benchsurf.Productid = products.ID
  INNER JOIN Customer
      ON Benchsurf.custid = Customer.ID
  WHERE Benchsurf.datequoted BETWEEN @date1 AND @date2 AND
        Benchsurf.custid = @custid

  Hope this helps.

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  • Edited by TgaLamx on Feb 20, 2013 01:33 AM
  • Edited by TgaLamx on Feb 22, 2013 01:24 AM
• • Reply
  

  TgaLamx

  Member

  25 Points

  61 Posts

  Re: Summing a total from a select statmement that need distinct in it

  Feb 20, 2013 01:35 AM|LINK

  Thanks just have to put in Distinct and change where condition and it worked great,

  If you were in tauranga I'd buy you a beer :),

   

• • Reply
  

  TgaLamx

  Member

  25 Points

  61 Posts

  Re: Summing a total from a select statmement that need distinct in it

  Feb 22, 2013 01:24 AM|LINK

  This procedure is retuning the DISTINCT records but summing the total records any way of making the sum calc only the records returned?

  ALTER PROCEDURE dbo.getcompprod
  @custid int, @prod int, @bra varchar(50), @fin varchar(50), @type int
  
  	/*
  	(
  	@parameter1 int = 5,
  	@parameter2 datatype OUTPUT
  	)
  	*/
  AS
  	/* SET NOCOUNT ON */
  	DECLARE @comp varchar(50)
  	SET @comp = (SELECT Company FROM Customer WHERE (ID = @custid))
  
  	IF (@type = 0)
  	Begin
  	
  	SELECT DISTINCT
      Benchsurf.datequoted,
      Benchsurf.Jobnum,
      products.Product,
      Benchsurf.Brand,
      Benchsurf.colourshade,
      Benchsurf.Finish,
      Customer.username,
      ISNULL(SUM(ISNULL(Benchsurf.PriceMark, 999)) OVER(), 9999) AS TOTAL,
      Benchsurf.PriceMark
  FROM Benchsurf
  INNER JOIN products
      ON Benchsurf.Productid = products.ID
  INNER JOIN Customer
      ON Benchsurf.custid = Customer.ID
  WHERE Benchsurf.Productid = @prod  AND
           (Customer.Company = @comp)
  		 END
  
  IF (@type = 1)
  	Begin
  	
  	SELECT DISTINCT
      Benchsurf.datequoted,
      Benchsurf.Jobnum,
      products.Product,
      Benchsurf.Brand,
      Benchsurf.colourshade,
      Benchsurf.Finish,
      Customer.username,
      ISNULL(SUM(ISNULL(Benchsurf.PriceMark, 0)) OVER(), 0) AS TOTAL,
      Benchsurf.PriceMark
  FROM Benchsurf
  INNER JOIN products
      ON Benchsurf.Productid = products.ID
  INNER JOIN Customer
      ON Benchsurf.custid = Customer.ID
  WHERE  Benchsurf.Productid = @prod  AND  Benchsurf.Finish = @fin AND   (Customer.Company = @comp)
  		 END
  
  		 IF (@type = 2)
  	Begin
  	
  	SELECT DISTINCT
      Benchsurf.datequoted,
      Benchsurf.Jobnum,
      products.Product,
      Benchsurf.Brand,
      Benchsurf.colourshade,
      Benchsurf.Finish,
      Customer.username,
      ISNULL(SUM(ISNULL(Benchsurf.PriceMark, 0)) OVER(), 0) AS TOTAL,
      Benchsurf.PriceMark
  FROM Benchsurf
  INNER JOIN products
      ON Benchsurf.Productid = products.ID
  INNER JOIN Customer
      ON Benchsurf.custid = Customer.ID
  WHERE  Benchsurf.Productid = @prod  AND  Benchsurf.Brand = @bra AND   (Customer.Company = @comp)
  		 END
  
  		 IF (@type = 3)
  	Begin
  	
  	SELECT DISTINCT
      Benchsurf.datequoted,
      Benchsurf.Jobnum,
      products.Product,
      Benchsurf.Brand,
      Benchsurf.colourshade,
      Benchsurf.Finish,
      Customer.username,
      ISNULL(SUM(ISNULL(Benchsurf.PriceMark, 0)) OVER(), 0) AS TOTAL,
      Benchsurf.PriceMark
  FROM Benchsurf
  INNER JOIN products
      ON Benchsurf.Productid = products.ID
  INNER JOIN Customer
      ON Benchsurf.custid = Customer.ID
  WHERE  Benchsurf.Productid = @prod  AND  Benchsurf.Brand = @bra AND Benchsurf.Finish = @fin  AND   (Customer.Company = @comp)
  		 END
  
  	RETURN

  Thanks

  Glen

• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Summing a total from a select statmement that need distinct in it

  Feb 22, 2013 01:44 PM|LINK

  Try something like this:

  with CTE_D as
  (
      SELECT DISTINCT
          Benchsurf.datequoted,
          Benchsurf.Jobnum,
          products.Product,
          Benchsurf.Brand,
          Benchsurf.colourshade,
          Benchsurf.Finish,
          Customer.username,
          Benchsurf.PriceMark
      FROM Benchsurf
      INNER JOIN products
          ON Benchsurf.Productid = products.ID
      INNER JOIN Customer
          ON Benchsurf.custid = Customer.ID
      WHERE Benchsurf.datequoted BETWEEN @date1 AND @date2 AND
            Benchsurf.custid = @custid
  )
  
  SELECT
      *,
      ISNULL(SUM(ISNULL(PriceMark, 0)) OVER(), 0) AS TOTAL
  FROM CTE_D

  Hope this helps.

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  • Marked as answer by TgaLamx on Feb 24, 2013 09:31 PM
• • Reply
  

  TgaLamx

  Member

  25 Points

  61 Posts

  Re: Summing a total from a select statmement that need distinct in it

  Feb 24, 2013 09:31 PM|LINK

  thanks again for your help,

  :)