I'm new to SQL and thinking is there any way to simplify this Query?

Question

Im really new to SQL, doing this for my basic needs for works and basically I'm not a tech guy. But I'm curious to know is there any way to simplify my SQL Code here:

SET sql_mode = '';


SELECT DISTINCT partner.kode_agent,
                institusi.nama,
                count(proposal.kode_proposal) AS 'Jumlah Proposal',
                sum(project.jumlah_pencairan) AS 'Jumlah Pencairan'
FROM brw_proposals proposal
JOIN ms_agent partner ON proposal.kode_agent = partner.kode_agent
JOIN ms_agent_institusi institusi ON partner.kode_institusi = institusi.kode_institusi
JOIN ms_projects project ON proposal.kode_proposal = project.kode_proposal
WHERE institusi.nama like 'BMT%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 10
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 10
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 10
GROUP BY institusi.nama

Cause I guess it's too much with the WHERE clause and I'm not so familiar with subquery.

Hope anyone can share their experience to improve my code! Thanks

Welcome to SO. Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jun 07 '20 at 15:25
You have `GROUP BY institusi.nama` but are selecting both `partner.kode_agent` and `institusi.nama,` - which `partner.kode_agent` are you hoping to get if there is more than one in a group? — Martin Smith, Jun 07 '20 at 15:32

Alpha 1 · Answer 1 · 2020-06-07T15:37:56.910

3

Here is the simplified query:

SELECT DISTINCT partner.kode_agent,
                institusi.nama,
                count(proposal.kode_proposal) AS 'Jumlah Proposal',
                sum(project.jumlah_pencairan) AS 'Jumlah Pencairan'
FROM brw_proposals proposal
JOIN ms_agent partner ON proposal.kode_agent = partner.kode_agent
JOIN ms_agent_institusi institusi ON partner.kode_institusi = institusi.kode_institusi
JOIN ms_projects project ON proposal.kode_proposal = project.kode_proposal
WHERE (institusi.nama like 'BMT%' OR institusi.nama like 'Ksp%' OR institusi.nama like 'Baitul Maal Wat%') and proposal.status_proposal_id IN (4,8,3,10)
GROUP BY institusi.nama

You can use IN clause to match muliple values like

proposal.status_proposal_id IN (4,8,3,10)

edited Jun 07 '20 at 15:37

answered Jun 07 '20 at 15:17

Alpha 1

4,118
2
17
23

2

You can go one step further with `institusi.nama REGEXP '^BMT|^Ksp|^Baitul Maal Wat'` – TomNash Jun 07 '20 at 15:21
1

The query will only be optimized, when all indexes that are needed to make this query performant are in place...... This answer only gives a shorter version of the original query. – Luuk Jun 07 '20 at 15:31
@Luuk He mentioned in the question; he wanted to simplify the SQL Query. – Alpha 1 Jun 07 '20 at 15:35

score 0 · Answer 2 · answered Jun 07 '20 at 15:32

Here's an example of a valid query. There isn't enough information to say whether or not it satisfies your requirements...

SELECT p.kode_agent
     , i.nama
     , COUNT(k.kode_proposal) 'Jumlah Proposal'
     , SUM(j.jumlah_pencairan) 'Jumlah Pencairan'
  FROM brw_proposals k
  JOIN ms_agent p 
    ON k.kode_agent = p.kode_agent
  JOIN ms_agent_institusi i
    ON i.kode_institusi = p.kode_institusi 
  JOIN ms_projects j
    ON k.kode_proposal = j.kode_proposal
 WHERE k.status_proposal_id IN(4,8,3,10)
   AND ( i.nama like 'BMT%'
      OR i.nama like 'Baitul Maal Wat%'
      OR i.nama like 'Ksp%' 
       )
 GROUP 
    BY i.nama
     , p.kode_agent;

I'm new to SQL and thinking is there any way to simplify this Query?

2 Answers2