CODEWITHSUNDEEP
mysql
DonnellyOverflow
DonnellyOverflow

Reputation: 4195

Reduce MySQL Code down or combine SELECT Statements

I have made a few relations to do with a banking database system. this is my current code. The table has 

SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000001;
SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000002;
SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000003;

the rest of the code is a duplicate of this part with the next type of 'Account' and looping back through sortcode's 1-3 again.

I was wondering if there was a more elegant way of producing this. I need to count the number of student, current and saver accounts for each bank.

Or is there a way to combine lots of selects together to make a neat table?

Upvotes: 0

Views: 33

Answers (2)

DRapp
DRapp

Reputation: 48129

You could also apply a PIVOT approach to this query to always return a single row and know the fixed-final columns of the result set. However, applying a group by allows for more flexibility of returned rows, especially if you have a large amount of individual things you are trying to tally up.

select
      A.AccountType,
      SUM( IF( A.SortCode = 1, 1, 0 )) as SortCode1Cnt,
      SUM( IF( A.SortCode = 2, 1, 0 )) as SortCode2Cnt,
      SUM( IF( A.SortCode = 3, 1, 0 )) as SortCode3Cnt
   from
      Account A
   where
      A.AccountType = 'Student'
      AND A.SortCode IN ( 1, 2, 3 )
   group by
      A.AccountType

Note... it appears your sort code is a numeric as you have no quotes around indicating a character string. So, all the leading zeros are irrelevant. And if you were only doing based on a single Account Type, you don't even need the leading Account Type column and can remove the group by too.

Upvotes: 0

Idan Arye
Idan Arye

Reputation: 12603

That's what GROUP BY is for!

SELECT SortCode,COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
GROUP BY SortCode;

UPDATE:

You can also GROUP BY with multiple grouping fields:

SELECT SortCode,AccountType,COUNT(AccountType) AS Student_Total FROM Account
GROUP BY SortCode,AccountType;

Upvotes: 2

Related Questions