0

Table data:

id servicetype value taxid customerid comment docid itemid
1451806 1 -56.10 2 6788 Monthly subscription 04/2023 (Internet - AlfaNET 500/500 w pakiecie) 730818 1
1451807 5 -53.90 3 6788 Monthly subscription 04/2023 (Telewizja - AlfaTV pakiet Plus Korzystny) 730818 2
1451808 5 -15.00 3 6788 Monthly subscription 04/2023 (Telewizja - Multiroom 2021) 730818 3
1451809 1 -59.90 2 6789 Monthly subscription 04/2023 (Internet - AlfaNET 300/300) 730819 1

Questions:

  1. Select all rows where field customerid must have at least two rows which includes at least one row with taxid=2 and taxid=3 all has the same docid

Example: for customerid=10 there exist at least one row with taxid=2 and taxid=3 and all of them has docid=1234

  1. Then sum value the previously selected rows depending on the TAXID value.
1
  • #1) I'm no DB expert. My main problem is part when I must select only curomersid where it must contains at least one row with taxid=2 and taxid=3 #2) customerid=10 is just example. I need all customers where condition with taxid is met.
    – kyob
    Mar 7, 2023 at 13:02

3 Answers 3

1

Use a subquery to identify the customerid and docid of interest, then join it with the table to get the sum for every taxid:

SELECT customerid, docid, taxid, SUM(value)
FROM table 
JOIN (SELECT customerid, docid
      FROM table
      GROUP BY customerid, docid
      HAVING COUNT(*) FILTER (WHERE taxid=2) > 0 
         AND COUNT(*) FILTER (WHERE taxid=3) > 0) AS t2 
ON (table.customerid = t2.customerid and table.docid = t2.docid)
GROUP BY customerid, docid, taxid

If you only need a total for those two values of taxid, you can use this query, without group by:

SELECT SUM(value) FILTER (WHERE taxid=2) as vat23, 
       SUM(value) FILTER (WHERE taxid=3) as vat8
FROM table 
JOIN (SELECT customerid, docid
      FROM table
      GROUP BY customerid, docid
      HAVING COUNT(*) FILTER (WHERE taxid=2) > 0 
         AND COUNT(*) FILTER (WHERE taxid=3) > 0) AS t2 
ON (table.customerid = t2.customerid and table.docid = t2.docid);
0
0

With https://dba.stackexchange.com/a/324470/63340 answer my final query looks like that. Don't know if it's optimal but works as expected.

WITH cashvat (vat23, vat8) AS
  (SELECT c.customerid,
          c.docid,
          c.taxid,
          SUM(c.value) AS suma
   FROM cash c
   JOIN
     (SELECT cc.customerid,
             cc.docid
      FROM cash cc
      GROUP BY cc.customerid,
               cc.docid
      HAVING COUNT(*) FILTER (
                              WHERE cc.taxid=2) > 0
      AND COUNT(*) FILTER (
                           WHERE cc.taxid=3) > 0) AS t2 ON (c.customerid = t2.customerid
                                                            AND c.docid = t2.docid)
   GROUP BY c.customerid,
            c.docid,
            c.taxid)
SELECT SUM(CASE
               WHEN taxid=2 THEN suma
               ELSE 0
           END) AS vat23,
       SUM(CASE
               WHEN taxid=3 THEN suma
               ELSE 0
           END) AS vat8
FROM cashvat
0

You can do this with a single scan of the base table, using conditional aggregation. No joins are needed.

SELECT
  d.customerid,
  d.docid,
  SUM(d.value) FILTER (WHERE d.taxid = 2) AS total2,
  SUM(d.value) FILTER (WHERE d.taxid = 3) AS total3  
FROM data d
GROUP BY
  d.customerid,
  d.docid
HAVING COUNT(*) FILTER (WHERE d.taxid = 2) > 0
   AND COUNT(*) FILTER (WHERE d.taxid = 3) > 0;

db<>fiddle

The exact grouping you want is somewhat unclear, you may want to modify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.