Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Unanswered: Error on query sql server

    Hello I have an error on this query with sql server, how can I sum the Quantita of items with the same Codart:


    Click image for larger version. 

Name:	Domanda 2.png 
Views:	13 
Size:	294.5 KB 
ID:	17267

    Code:
    Select Articolo.CodArt,Articolo.Descrizione,Articolo.UM,Articolo.Prezzo,DistintaSemplice.Quantita
    from Fatture.dbo.Preventivo
    inner join Fatture.dbo.Capitolo
    on Capitolo.IdPreventivo=Preventivo.IdPreventivo
    inner join Fatture.dbo.SottoCapitolo
    on SottoCapitolo.IdCapitolo=Capitolo.IdCapitolo
    inner join Fatture.dbo.Distinta
    on SottoCapitolo.Codice=Distinta.IdDistinta
    inner join Fatture.dbo.DistintaSemplice
    on DistintaSemplice.IdDistinta=Distinta.IdDistinta
    inner join Fatture.dbo.Articolo
    on Articolo.IdArticolo=DistintaSemplice.IdArticolo
    
    where SottoCapitolo.Tipo='Distinta'
    Last edited by rikideveloper; 01-25-17 at 02:47.

  2. #2
    Join Date
    Oct 2007
    Posts
    156
    Provided Answers: 9
    you don't specify what error you are having, so kind of hard for us to just guess. typo? missing table? corrupt database? I would say one of the big things wrong with the SQL based on your description, is that you don't have a SUM(of_a_column) and there is no GROUP BY of_other_columns.

  3. #3
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Try this

    SUM(Quantita) OVER (PARTITION BY Codart )

  4. #4
    Join Date
    Mar 2013
    Posts
    14
    never mind

  5. #5
    Join Date
    Mar 2017
    Posts
    2
    Quote Originally Posted by rikideveloper View Post
    Hello I have an error on this query with sql server, how can I sum the Quantita of items with the same Codart:


    Click image for larger version. 

Name:	Domanda 2.png 
Views:	13 
Size:	294.5 KB 
ID:	17267

    Code:
    Select Articolo.CodArt,Articolo.Descrizione,Articolo.UM,Articolo.Prezzo,DistintaSemplice.Quantita
    from Fatture.dbo.Preventivo
    inner join Fatture.dbo.Capitolo
    on Capitolo.IdPreventivo=Preventivo.IdPreventivo
    inner join Fatture.dbo.SottoCapitolo
    on SottoCapitolo.IdCapitolo=Capitolo.IdCapitolo
    inner join Fatture.dbo.Distinta
    on SottoCapitolo.Codice=Distinta.IdDistinta
    inner join Fatture.dbo.DistintaSemplice
    on DistintaSemplice.IdDistinta=Distinta.IdDistinta
    inner join Fatture.dbo.Articolo
    on Articolo.IdArticolo=DistintaSemplice.IdArticolo
    
    where SottoCapitolo.Tipo='Distinta'
    If you want to sum the quantitia then you should use the function sum. After sum you will need to group by with the values you mentioned in your question.

  6. #6
    Join Date
    Mar 2004
    Posts
    163
    might be a copy and paste error but you seems to be missing a ' at the very end of your code

    where SottoCapitolo.Tipo='Distinta
    where SottoCapitolo.Tipo='Distinta'

  7. #7
    Join Date
    May 2017
    Posts
    1

    Use Group By clause and SUM

    Assuming the closing single quote (') was not your answer
    Add a Group By Clause and a SUM to your query.
    ALL individual (distinct) fields must be in the Group By statement
    Then other fields can be manipulated with SUM, MIN, MAX, AVG,...

    TRY::::

    Select Articolo.CodArt,Articolo.Descrizione,Articolo.UM,A rticolo.Prezzo,SUM(DistintaSemplice.Quantita) as Quantita, Count(DistintaSemplice.Quantita) as QuantitaCnt
    from Fatture.dbo.Preventivo
    inner join Fatture.dbo.Capitolo
    on Capitolo.IdPreventivo=Preventivo.IdPreventivo
    inner join Fatture.dbo.SottoCapitolo
    on SottoCapitolo.IdCapitolo=Capitolo.IdCapitolo
    inner join Fatture.dbo.Distinta
    on SottoCapitolo.Codice=Distinta.IdDistinta
    inner join Fatture.dbo.DistintaSemplice
    on DistintaSemplice.IdDistinta=Distinta.IdDistinta
    inner join Fatture.dbo.Articolo
    on Articolo.IdArticolo=DistintaSemplice.IdArticolo

    where SottoCapitolo.Tipo='Distinta'
    Group By Articolo.CodArt,Articolo.Descrizione,Articolo.UM,A rticolo.Prezzo

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •