The real situation is bigger, but I will model it like this. There are tables Name (ID, Name) and Cost (ID, Cost). You need to link them across the ID field and output the bag of goods, but you need to output not the ID but the Name. You need something like this only working)

select Name.Name, SUM(Cost.Cost) from Name join Cost on Name.ID = Cost.ID group by Name.ID

If you take the real task, that is, the tables TOV, DMZ, DMS

You need to output TOV.NTOV , SUM(DMS.KOL) , SUM(DMS.CENA * DMS.KOL) (linking the tables by the fields TOV.KTOV = DMS.KTOV and DMZ.NDM = DMS.NDM ) by the given date (for example, ' 20140501 ') and by DMZ_.PR = 2 , grouping by TOV.KTOV .

Explanations: TOV - goods (identifier, name, type), DMZ - documents (date, number, type (arrival, departure)), DMS - delivery (which product, according to which document, quantity, price, type (I know what is not needed duplicate, but the task is set))

  • one
    since for one KTOV you are guaranteed only one NTOV, then simply add NTOV to group by this will not affect the result. Although of course it will be strange to look two lines of "beer" with some kind of data relating to different KTOV. but you have a crookedly formulated task, you can’t do anything else - Mike
  • You just need to separate both fields separated by a comma in group by and get 3 lines of which 2 will be 'beer'. Or do you expect to get some other result? If so, please indicate what specific result you want to see based on the data shown. And never show the input in the form of a picture. In the text, at least you can quickly create a test database, and in the picture you need to create tables with your hands and fill them. - Mike
  • if you specify 2 lines through a comma - Rusl
  • it can't be. Give an example on sqlfiddle.com which gives two lines when grouping by group by KTOV, NTOV - Mike
  • Thanks, earned. I checked just for bad data where the answers matched. - Rusl

1 answer 1

You must group by the field to which you use the aggregation function, so in your case you just need to group by Name.Name :

 select n.Name, t.cost_sum from (select Name.ID, SUM(Cost.Cost) as cost_sum from Name join Cost on Name.ID = Cost.ID group by Name.ID) t join Name n ON t.id = n.id
  • That's exactly what I need to group by ID - Rusl
  • @Rusl why group by ID if Name.Name is the result of the answer? Show an example of the data table to understand why you need it. - Denis
  • so set the task - Rusl
  • @Rusl updated the answer - Denis
  • oh yeah, another problem is that you cannot use subqueries - Rusl