How do I include a grouped subquery in my sql result?

Question

I have a display table that churns out daily result in the form of mysql. The daily result does not include products that are not making any sales today. How do i read from the table and determine to list all products even if a particular product is not making a sale that particular day but exists on the table on another day.

I researched and understand that using EXIST on the WHERE clause works. But i tried but to no avail. I think I just have to include a grouped sub query inside a query but where should i put it. Below is an example of my code

SELECT 
transaction.transactionservicetype AS Services,
COUNT(transaction.transactionid) AS Count,
IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM transaction
RIGHT JOIN statement ON transaction.transactionid = statement.transactionid
WHERE transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
GROUP BY `transaction`.transactionservicetype ASC

Instead of displaying the table such as this:

Services   Count   Amount   Netcost   Total Earning

Chicken   4       5.30       5.14           -

Beef      3       3.30       3.13          -

I want the result to include products not found on the same day but determine another type of products had existed on the table thus displaying the result as such:

Services   Count   Amount   Netcost   Total Earning

Chicken        4   5.30        5.14       -

Beef           3   3.30        3.13       -

Venison        0     0           0        -

Fowl           0     0           0        -

Update:

I did not get the correct outcome using anti join and the other results. Scanning thru and using Ultimater's code as an example, this is as close to what i'd like to get from the result using sql query:

(
SELECT 
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
  IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM
  transaction RIGHT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
GROUP BY
  `transaction`.transactionservicetype ASC
)
UNION
(
SELECT 
  transactionservicetype AS Services,
    '0' AS Count,
    '0' AS Amount,
  '0' AS NetCost,
  '0' AS TotalEarning
FROM
  transaction
GROUP BY
  transactionservicetype ASC
)

The above sql query got me a bit closer to what I wanted from my outcome. And this is the result:

Services   Count   Amount   Netcost   Total Earning

Chicken        4   5.30        5.14       -

Beef           3   3.30        3.13       -

Chicken        0     0           0        -

Beef           0     0           0        -

Venison        0     0           0        -

Fowl           0     0           0        -

I just have to remove the duplicated rows (Chicken, Beef) how do i fix it using sql query?

You're starting from the statement then joining onto the transaction. Might be as simple as changing the RIGHT JOIN to a LEFT JOIN if I'm not drunk... — Ultimater, Apr 30 '19 at 04:15
Otherwise I'd look into a `UNION ALL` with the results of an anti-join. The idea you'd start with the `transaction` table and join it onto the `statement` table using the same ON clause `ON transaction.transactionid = statement.transactionid` but `WHERE statement.statementid IS NULL` assuming statementid is your primary key. — Ultimater, Apr 30 '19 at 04:30

Ultimater · Accepted Answer · 2019-05-01T06:05:22.437

Untested but pretty sure you're looking for something like this:
The idea being you'd UNION ALL your current results onto an anti-join to get the remaining results to simulate a FULL JOIN:

(
SELECT 
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
  IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM
  transaction RIGHT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
GROUP BY
  `transaction`.transactionservicetype ASC
)

UNION ALL

(
SELECT
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  '0' AS NetCost,
  '0' as TotalEarning
FROM
  transaction LEFT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  statement.statementid IS NULL AND transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS'
GROUP BY
  `transaction`.transactionservicetype ASC
);

You could also leave off the transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' on the anti-join depending on the criteria where you want to grab such "missing" services.

The above also assumes transaction.transactionid is an existing column and a primary key.

The second part is the anti-join:

SELECT
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  '0' AS NetCost,
  '0' as TotalEarning
FROM
  transaction LEFT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  statement.statementid IS NULL AND transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS'
GROUP BY
  `transaction`.transactionservicetype ASC

You should be able to run this by itself and see results such as this:

Services   Count   Amount   Netcost   Total Earning

Venison        0     0           0        0

Fowl           0     0           0        0

I wrote the anti-join by changing the RIGHT JOIN to a LEFT JOIN, specifying statement.statementid IS NULL in the WHERE clause so you don't get any INNER JOIN data you already have from the previous query, and I adjusted the counting logic such as NetCost and TotalEarnings by hard-coding those since there'd be no matches for those in such an anti-join in order to speed up its logic.

Using an anti-join for the purposes of simulating a full join in MySQL is covered in this answer here:
https://stackoverflow.com/a/4796911/466314

An anti-join is a term used in the post I linked to, but not really an official term. The term "anti" here means there needs to be an original join, then you want to do another join but grab the inverse of that join when masked to a full join. This is different from merely combining the results of a LEFT and RIGHT (outer) join with each other because they share the same INNER JOIN data and you'd have redundant results.

A UNION ALL allows that sort of redundancy. A UNION by itself removes those duplicates.

If you want to rely upon UNION to remove those duplicates, you can simulate an easier to understand FULL JOIN like so:

(
SELECT 
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
  IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM
  transaction RIGHT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
GROUP BY
  `transaction`.transactionservicetype ASC
)

UNION

(
SELECT 
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
  IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM
  transaction LEFT JOIN statement
ON
  transaction.transactionid = statement.transactionid
WHERE
  transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
GROUP BY
  `transaction`.transactionservicetype ASC
);

All I did was wrote the same original query again but changed the term RIGHT to LEFT, and put them between a UNION to remove duplicates when joining the results together.

Edit:

Here's the latest suggestion: Use the original query, but change from a RIGHT JOIN to a LEFT JOIN, then move the entire WHERE clause to the ON clause.

SELECT 
  transaction.transactionservicetype AS Services,
  COUNT(transaction.transactionid) AS Count,
  IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
  IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
  IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
FROM
  transaction LEFT JOIN statement
ON
  transaction.transactionid = statement.transactionid AND
  transaction.transactiondate = '2019-04-03' AND
  transaction.transactionstatus = 'SUCCESS' 
GROUP BY
  `transaction`.transactionservicetype ASC

As per the results given to me, we can modify the almost attempt and merely do a subquery on the results, and a group by to get the desired output:

SELECT r.Services as 'Services',SUM(r.Count) as 'Count', SUM(r.Amount) as 'Amount',SUM(r.NetCost) as 'NestCost', SUM(r.TotalEarnings) AS 'TotalEarnings'
FROM
(
    SELECT 
      transaction.transactionservicetype AS Services,
      COUNT(transaction.transactionid) AS Count,
      IFNULL (SUM(transaction.transactionamount),'0') AS Amount,
      IFNULL (SUM(statement.statementdebit),'0') AS NetCost,
      IFNULL((SUM(transaction.transactionamount) - SUM(statement.statementdebit)),'0') as TotalEarning
    FROM
      transaction RIGHT JOIN statement
    ON
      transaction.transactionid = statement.transactionid
    WHERE
      transaction.transactiondate = '2019-04-03' AND transaction.transactionstatus = 'SUCCESS' 
    GROUP BY
      `transaction`.transactionservicetype ASC

    UNION

    SELECT 
      transactionservicetype AS Services,
        '0' AS Count,
        '0' AS Amount,
      '0' AS NetCost,
      '0' AS TotalEarning
    FROM
      transaction
    GROUP BY
      transactionservicetype ASC
) r
GROUP BY r.Services;

`UNION ALL` takes a toll on executing time and ended up timeout. How do u skip to just using `FULL JOIN` — Anonymous Vagrant, Apr 30 '19 at 05:41
@AnonymousVagrant Try running the anti-join by itself and compare it to your original query. The anti-join should be faster because there's less counting stuff going on... Could technically run it as two separate queries if you're running into timeout issues, then join the results in your program. Otherwise, could UNION ALL first to achieve a FULL JOIN, alias it, them do all your complex work in a subquery. — Ultimater, Apr 30 '19 at 06:02
@AnonymousVagrant I updated answer. Make sure to refer to both code snippets in the answer here before trying to figure out how I wrote my adjustments: https://stackoverflow.com/a/4796911/466314 Let me know if you have more questions. — Ultimater, Apr 30 '19 at 08:51
Updated the answer with my outcome. I tried ur method on the anti-join and did not get the outcome i wanted. But i'm close to getting it using my sql query based on ur method. — Anonymous Vagrant, May 01 '19 at 03:06
@AnonymousVagrant I moved your edit to question so the question has more details for other people looking at question. I updated my answer with another suggestion. The idea being swap from a RIGHT JOIN to a LEFT JOIN and move your whole WHERE clause to the ON clause as extra conditions. — Ultimater, May 01 '19 at 05:18
Thanks. The latest suggested query does not work on `LEFT JOIN` as the query got timed out, however changing it to `RIGHT JOIN` yields a different result in that a new row containing `null` appears . It still does not display other results from other days which is `(Venison, Fowl)` — Anonymous Vagrant, May 01 '19 at 05:29
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192658/discussion-between-ultimater-and-anonymous-vagrant). — Ultimater, May 01 '19 at 05:37

How do I include a grouped subquery in my sql result?

Update:

1 Answers1