How to take values ​​from a column in a table to postgres and sum them up?

Question

PostgreSQL

There is a Spring server and a PostgreSQL database, as well as two tables [with fields] production[id, prodname, prodcount], admin_productions[id, adminproductname, adminproducttotal]. In admin_productions, the admin adds products, and in production, the name and quantity of the manufactured product are added. You need to do this:

select prodcount from production inner join admin_productions on production.prodname = admin_productions.adminproductname

here is a link to the github
and somehow sum up prodcount and show at least in the terminal

. Another problem is that prodcount is stored as a String and you need to make it Integer
and reflect the ReportController

Answer 1

crossing crutches and my capabilities, I got this, now I need it to be more or less convenient, mb combine them into one class

package com.example.beton.controller;

import com.example.beton.domain.AdminProductions;
import com.example.beton.domain.Production;
import com.example.beton.domain.Sales;
import com.example.beton.repos.AdminProductRepo;
import com.example.beton.repos.ProductionRepo;
import com.example.beton.repos.SaleRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import java.lang.reflect.Array;
import java.util.List;
import java.util.Map;

@Controller
public class ReportController {
    @Autowired
    private SaleRepo saleRepo;

    @Autowired
    private AdminProductRepo adminProductRepo;

    @Autowired
    private ProductionRepo productionRepo;

    @GetMapping("/report")
    public String getShowReports(Map<String, Object> model){
//        производимые продукты
        String[] adminProductArray = new String[20];
//        массив количества производимых товаров
        Integer[] prices = new Integer[20];
//        массив проданных товаров
        Integer[] salecount = new Integer[20];
//        массив цен на проданные изделия
        Double[] saletotal = new Double[20];

        /*##############################################*/
        /*############## сверху Массивы ################*/
        /*##############################################*/


//        Получение списка производимых продуктов
        Integer count = 0;
        Iterable<AdminProductions> adminprod = adminProductRepo.findAll();
        for (AdminProductions admprd : adminprod){
            adminProductArray[count] = admprd.getAdminproductname();
            count++;
        }

//        Получение количества производимых продуктов
        Integer i = 0;
        for (String ad : adminProductArray) {
            Integer prCount=0;

            if (ad!=null){
                Iterable<Production> prod = productionRepo.findByProdname(ad);

                for (Production pr : prod) {
                    prCount += Integer.parseInt(pr.getProdcount());
                }
                prices[i] = prCount;
//                System.out.println(ad +" : "+ prCount);
                i++;
            }
        }

//        Получение количества проданных товаров
        Integer j = 0;
        for (String sp : adminProductArray) {
            Integer slCount=0;

            if (sp!=null && !sp.equals("Количество не указано")){
                Iterable<Sales> sales = saleRepo.findBySalename(sp);

                for (Sales sl : sales) {
                    slCount += Integer.parseInt(sl.getSalecount());
                }
                salecount[j] = slCount;
//                System.out.println(sp +" : "+ slCount);
                j++;
            }
        }

//        Получение цен на проданные товары
        Integer foo = 0;
        for (String st : adminProductArray) {
            Double stCount = 0.0;

            if (st!=null){
                Iterable<Sales> salestotal = saleRepo.findBySaletotal(st);

                for (Sales slt : salestotal) {
//                    stCount += Double.parseDouble(slt.getSaletotal());
                    System.out.println(slt.getSalename());
                }
                saletotal[foo] = stCount;
//                System.out.println(st +" : "+ stCount);
                foo++;
            }
        }

        model.put("ad", adminProductArray);
        model.put("prc", prices);
        model.put("slcnts", salecount);
        model.put("sattls", saletotal);

        return "report";
    }
    @GetMapping("/reportsuper")
    public String superShowReport(Map<String, Object> model){
        return "superreport";
    }
}

package com.example.beton.repos;

import com.example.beton.domain.Production;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface ProductionRepo extends CrudRepository<Production, Integer> {
    List<Production> findByProdname(String prodname);
}

<#import "parts/common.ftlh" as c>

<@c.page>

<div class="container">
    <div class="row">
        <div class="col-lg-2 col-md-2 col-sm-2 col-2">
            <#list ad as a>
                <#if a??>
                    <p>${a}</p>
                </#if>
            </#list>
        </div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1">
            <#list prc as pr>
                <#if pr??>
                    <p>${pr} шт.</p>
                </#if>
            </#list>
        </div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1">
            <#list slcnts as slcnt>
                <#if slcnt??>
                    <p>${slcnt} шт.</p>
                </#if>
            </#list>
        </div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1">
             <#list sattls as sattl>
                <#if sattl??>
                    <p>${sattl} руб.</p>
                </#if>
             </#list>
        </div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
        <div class="col-lg-1 col-md-1 col-sm-1 col-1"></div>
    </div>
</div>
    </tbody>
</table>

</@c.page>

But I would like it to be through iterable, so as not to open the list every time on the freemarker

Answer 2

1. If a number is stored in the field, then the number should be used as the type. For several reasons.
2.

select sum(prodcount::int) as cnt 
from production 
inner join admin_productions on production.prodname = admin_productions.adminproductname

3. This question is in no way difficult.