Good day! I need to get sales for all stocks. There are about 70 stocks in the database. At this time I am writing 70 requests)) Can I shorten my request? Below is one example of my requests.

select oa1.dept_id, COUNT(oa1.id) quantity1, sum(oa1.premium) 'sum1' from Table1 oa1 Left Join Table2 od1 On od1.id = oa1.dept_id where oa1.action_ID = 1 group by oa1.dept_id

They are all the same requests, there just ID changes.

  • What ID changing? oa1.action_ID ? Or what? - BOPOH
  • @ CROW, yes you are right oa1.action_ID is changing. - Shynaz Alish

5 answers 5

 select oa1.action_ID, oa1.dept_id, COUNT(oa1.id) quantity1, sum(oa1.premium) 'sum1' from Table1 oa1 Left Join Table2 od1 On od1.id = oa1.dept_id group by oa1.dept_id, oa1.action_ID having action_ID = 1 order by action_ID

Clean up having if you need to select all stocks. Parse lines for each of the shares on the side of the calling code. order by added to simplify parsing.

  • Thanks for your reply, it looks like the answer below. Your answer gives me all the shares in a row, and I need in the columns! - Shynaz Alish
  • 3
    @ ShynazAlish write in the query itself what you need. including output format and column names. do not make people guess. and at the same time specify what you have for sql - ms sql, mysql, something else? - PashaPash ♦
 select oa1.dept_id, COUNT(oa1.id) quantity1, sum(oa1.premium) 'sum1' from Table1 oa1 Left Join Table2 od1 On od1.id = oa1.dept_id where oa1.action_ID in (select distinct oa1.action_ID from Table1 oa1) group by oa1.dept_id;

Something like this.

  • Your answer inspired hope in me, but he did not disprove them)). I need each share to be in a separate column. That is, I have 70 shares, and the answer should contain a table with 141 columns)). And your query summarizes them and displays only 3 columns. dept_id does not repeat. - Shynaz Alish

You just need to omit the "WHERE". In this case, you will receive a complete table for your request.

  • And if then it will be required not everywhere, but for some IDs? It's better to use in anyway :) - sys1n4
  • uh, your solution exactly repeats the action with the lower WHERE, because usually ID is a unique index. If you have to use for some IDs, then it is better to arrange a one-to-many relationship and describe in the related table what data to output. - jaro1989
  • stackoverflow.com/questions/15745042/… - can be useful. - jaro1989
  • Try to write more detailed answers. Explain what is the basis of your statement? - Nicolas Chabanovsky ♦

If you want 141 columns, write 70 join'ov or 140 case'ov. Only this is unlikely to shorten your request. :-)

  • If mysql, then I do not see any other solutions, but if mssql, then you can use pivot - BOPOH

for example on 3 promotions:

 select oa1.dept_id, COUNT(case when action_ID = 1 then oa1.id end) quantity1, sum(case when action_ID = 1 then oa1.premium end) 'sum1', COUNT(case when action_ID = 3 then oa1.id end) quantity3, sum(case when action_ID = 3 then oa1.premium end) 'sum3' COUNT(case when action_ID = 5 then oa1.id end) quantity5, sum(case when action_ID = 5 then oa1.premium end) 'sum5' from Table1 oa1 Left Join Table2 od1 On od1.id = oa1.dept_id where oa1.action_ID in (1,3,5) group by oa1.dept_id

This is a pure universal sql, but in different subd it is possible to make pivot in different ways by collecting columns from rows.