My solution:
SELECT eb.Acct AS Acct, COUNT(GJ1.Acct) AS NumberofTrans, eb.endingdebitbalances AS EndingBalance
FROM EndingBalances AS eb LEFT JOIN GeneralJournal1 AS GJ1 ON eb.Acct = GJ1.Acct
WHERE (eb.EndingDebitBalances>50000) OR (eb.EndingDebitBalances<-50000)
GROUP BY eb.Acct, eb.endingdebitbalances
HAVING COUNT(GJ1.Acct)>=1000;
This was a great refresher for SQL! It was helpful to practice the concepts again.
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;
1 Like
This was a fun SQL practice!
SELECT eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances, COUNT(gj.ID) AS NumOfTransactions
FROM EndingBalances AS eb LEFT JOIN GeneralJournal as gj ON eb.Acct = gj.Acct
WHERE eb.Acct IS NOT NULL AND (eb.EndingDebitBalances >= 50000 OR eb.EndingDebitBalances <= -50000)
GROUP BY eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances
HAVING COUNT(gj.ID) >= 1000
ORDER BY COUNT(gj.ID) DESC;
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;
Here is my solution. After looking over the suggested solution, I am questioning my use of g.Acct instead of g.ID. What are some potential weaknesses that I might be overlooking in my use of g.Acct?
Thanks for this awesome challenge! It was especially great practice since I just learned LEFT JOINs.
1 Like
Here is my solution! This was great practice!
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
Nice practice to get back into SQL. This is what I got for my code:
SELECT endingbalances.acct AS Acct,
endingbalances.glacctdesc AS GLAcctDesc,
endingbalances.endingdebitbalances AS EndingDebitBalances,
COUNT(generaljournal.trans) AS NumOfTransactions
FROM endingbalances LEFT JOIN generaljournal ON generaljournal.acct = endingbalances.acct
WHERE (Abs(EndingDebitBalances) >= 50000)
GROUP BY generaljournal.acct
HAVING NumOfTransactions >= 1000
ORDER BY NumOfTransactions DESC
and here is what my output looked like:
1 Like
thanks for the problem!
SELECT e.Acct, e.EndingDebitBalances, e.GLAcctDesc, 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.EndingDebitBalances, e.GLAcctDesc
HAVING COUNT(g.ID) >= 1000
ORDER BY COUNT(g.ID) DESC;
1 Like
Since I am new to using Access, it was great figuring out how to import and use the data from the excel file. Here is what I wrote for my code along with my query result:
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;
This was my SQL to arrive at the solution
SELECT gj.Acct, eb.GLAcctDesc, eb.EndingDebitBalances, COUNT(gj.Trans) AS NumOfTransactions
FROM GeneralJournal AS gj INNER JOIN EndingBalances AS eb ON gj.Acct = eb.Acct
WHERE gj.Acct Is NOT NULL AND ABS(eb.EndingDebitBalances) >= 50000
GROUP BY gj.Acct, eb.GLAcctDesc, eb.EndingDebitBalances
HAVING COUNT(gj.Trans) >= 1000
ORDER BY COUNT(gj.Trans) DESC;
This was a good practice for a SQL beginner like me to apply all of the different terms I’ve learned in a simple way. Great example.
1 Like
This was a great activity that tested my knowledge tested my knowledge on the basics of SQL. This activity was challenging but I was grateful I got the chance to work through it.
1 Like
Thanks for the practice!! It was cool to see how SQL skills can be applied in an auditing environment. Heres the query I ended up with:
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(EndingDebitBalances) > 50000
GROUP BY e.Acct, GLAcctDesc, EndingDebitBalances
HAVING COUNT(g.ID)>=1000
ORDER BY COUNT(g.ID) DESC;
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;
Thanks for the SQL review! Here is my Query code:
SELECT a.Acct, a.GLAcctDesc, Count(b.ID) AS NumOfTransactions
FROM EndingBalances AS a RIGHT JOIN GeneralJournal AS b ON a.Acct = b.Acct
WHERE ABS(a.EndingDebitBalances)>=50000
GROUP BY a.Acct, a.GLAcctDesc, a.EndingDebitBalances
So here I have two possibilities. The first one I came up on my own. The other I used ChatGPT to generate the SQL. The ChatGPT was fun because it was wrong the first couple of times, but I was able to talk it through the specifics of the problem till it generated the correct answer!
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;
ChatGPT Solution:
SELECT
e.Acct,
e.GLAcctDesc,
e.EndingDebitBalances,
transaction_counts.NumOfTransactions
FROM
EndingBalances AS e
LEFT JOIN (
SELECT
Acct,
COUNT(ID) AS NumOfTransactions
FROM
GeneralJournal
GROUP BY
Acct
HAVING
COUNT(ID) >= 1000
) AS transaction_counts ON e.Acct = transaction_counts.Acct
WHERE
e.Acct IS NOT NULL
AND transaction_counts.NumOfTransactions >= 1000
AND ABS(e.EndingDebitBalances) >= 50000
ORDER BY
transaction_counts.NumOfTransactions DESC;
3 Likes
This was a fun challenge to practice SQL! 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;
Here is my solution. I followed along with the video to learn how to connect the tables and how to do the join in the SQL code. I thought this exercise was engaging and very informative on how I would use SQL which I’m just starting to learn in real life.
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(e.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
This was nice practice with SQL. My solution was:
SELECT eb.EndingDebitBalances, eb.Acct, COUNT(gj.Trans) AS NumberOfTransactions
FROM EndingBalances AS eb RIGHT JOIN GeneralJournal AS gj ON eb.Acct = gj.Acct
WHERE eb.EndingDebitBalances >50000 OR eb.EndingDebitBalances<-50000
GROUP BY eb.Acct, eb.EndingDebitBalances
HAVING COUNT(gj.Trans)>1000
ORDER BY eb.Acct, COUNT(gj.Trans);
After completing the query, I read some of the answers and learned about the ABS function that others used to take the absolute value. That would have simplified my query, something that I will keep in mind in future use of SQL!
This was a great practice!
SELECT e.Acct, e.GLAcctDecs, e.EndDebitBalance, COUNT(g.ID) AS NumberOfTransactions
FROM EndingBalances AS e Left JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndDebitBalance) >= 50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndDebitBalance
HAVING COUNT(g.ID) >= 1000
ORDER BY COUNT(g.ID) DESC;