Select rows where a field from another column can take two values

Question

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

2. Then sum value the previously selected rows depending on the TAXID value.

• kyob: #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.

Answer 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);

Answer 2

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

Answer 3

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.