34|SQL – Audit Attention

Challenges
, ,
63

SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000 AND E.Acct IS NOT NULL
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID)>=1000
ORDER BY COUNT (g.ID) DESC

Thanks!!

64

Here is my solution:

SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, Count(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE ABS(e.EndingDebitBalances) >= 50000 AND e.Acct IS NOT NULL
GROUP BY e.Acct
HAVING Count(g.ID) >= 1000
ORDER BY Count(g.ID) DESC;

65

SELECT e.Acct, COUNT(g.ID) AS NumOfTransactions, e.EndingDebitBalances, e.GLAcctDesc
FROM EndingBalances AS e
LEFT JOIN GeneralJournal AS g on e.Acct=g.Acct
WHERE Acct IS NOT NULL AND Abs(e.EndingDebitBalances)>=50000
GROUP BY e.Acct
HAVING (NumOfTransactions>=1000)
ORDER BY COUNT(g.ID) DESC

66

Thank you for making this problem. Here is my SQL code:
SELECT eb.Acct AS Account, eb.GLAcctDesc AS ACCName, COUNT(gj.Acct) AS NumOfTransactions, eb.EndingDebitBalances AS Ending
FROM EndingBalances AS eb LEFT JOIN GeneralJournal AS gj ON eb.Acct=gj.Acct
WHERE ABS(eb.EndingDebitBalances) >=50000 AND eb.Acct IS NOT NULL
GROUP BY eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances
HAVING COUNT(gj.ID) >=1000
ORDER BY COUNT(gj.Acct) DESC;

67

I had also forgotten about LEFT JOIN, I tried the problem without a join and it wasn’t working until I finally put the join in the SQL. It was great to review it again.

68

image
image1071×589 29.8 KB

This one probably took me longer than it should have but I enjoyed the challenge. Cool challenge, thanks!

69

Great work Parker, looks like we did our WHERE statement two different ways but cool that it all still works!

70

SELECT
e.Acct AS Account,
e.GLAcctDesc AS Description,
e.EndingDebitBalances AS EndingBalance,
COUNT(g.ID) AS TransactionsCount
FROM
EndingBalances e
LEFT JOIN
GeneralJournal g ON e.Acct = g.Acct
WHERE
e.Acct IS NOT NULL
AND ABS(e.EndingDebitBalances) >= 50000
GROUP BY
e.Acct,
e.GLAcctDesc,
e.EndingDebitBalances
HAVING
COUNT(g.ID) >= 1000
ORDER BY
COUNT(g.ID) DESC;

71

SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.Acct) AS NumofTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE ABS(e.EndingDebitBalances) >=50000 AND e.Acct IS NOT NULL
GROUP BY e.Acct, GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.Acct) >= 1000
ORDER BY COUNT(g.Acct) DESC

72

This was a great challenge. Cool to see how the filtering functions of SQL work on a realistic example like this. Thank you for your explanation, it helped me remember that you can order by multiple columns one after another.

SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS (e.EndingDebitBalances) >= 50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;

1 Like
73

SELECT e.acct, e.glacctdesc, e.endingdebitbalances, count(g.id) as numoftransactions
from endingbalances e
inner join generaljournal g on g.acct = e.acct
where abs(endingbalances.endingdebitbalances) >= 50000
group by g.acct
having numoftransactions >= 1000
order by numoftransactions desc

I got this to work on data.world but not on access. Thanks for the practice.

74

Thanks! Good SQL refresher!
Answer:
SELECT eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances, COUNT(g.ID) AS NumTrans
FROM EndingBalances AS eb LEFT JOIN GeneralJournal AS g ON eb.Acct = g.Acct
WHERE (eb.EndingDebitBalance>=50000) AND (eb.Acct) IS NOT NULL)
GROUP BY eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances
HAVING (((COUNT(g.ID))>=1000));

75

Here’s my solution:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;

76

SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE e.Acc IS NOT NULL AND ABS(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID)>=1000
ORDER BY COUNT(g.ID) DESC;