Vito Vito - 4 months ago 7
SQL Question

How to combine two different case sql into 1?

*This question has asked before in stackoverflow

.How to use if then statement on sql?

i have modified and asked again here *




1) "
SELECT officer, null, sum(mkt), Sum(Non), sum(ICP), (sum(mkt)+Sum(Non)+sum(ICP)) as total From [DB$] group by officer
" <= they are okay. they should be grouped by officer, as every officer have many records




2) For the field- mkt, it is not necessarily consisted of value in every record . When mkt consists of value, say 30, it means the officer conduct the field job .Hence, i should count his field hour -Totalmin .

officer mkt Totalmin
S29 30 50
S29 60
S29 70 80
S29 80
S55 30 80
S55 60
S55 70 80
S55 80


Hence , the result should be :

officer sum(totalmin)
s29 130
s55 160


enter image description here

You can imagine here . if the Mkt field consist of numbers ,the total min will be added up and become the min-required .

in the picture ,the min-required output should be
45+40=85
group by officer (staffid-186 )

How to write this sql?




And, the problem is i have to combine these two cases in same sql. How can i?

Answer

you can use sub-query in select for such task:

Sub SQL()
Dim cn As Object: Set cn = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
Dim tbl1$, QT, strFile$, strCon$, strSQL$

strFile = ThisWorkbook.FullName 'if not active file then specify path!!!

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
cn.Open strCon

tbl1 = "[Sheet1$" & Sheets("Sheet1").UsedRange.Address(0, 0) & "]" 'replace sheet1 with required sheet name

strSQL = "SELECT  o.officer ," & _
         "        NULL ," & _
         "        SUM(o.mkt) ," & _
         "        SUM(o.Non) ," & _
         "        SUM(o.ICP) ," & _
         "        ( SELECT    SUM(i.Totalmin) FROM " & tbl1 & " AS i " & _
         "          WHERE i.Mkt > 0 AND i.officer = o.officer) AS TotalMin " & _
         "        FROM    " & tbl1 & " AS o " & _
         "        GROUP BY o.officer"

rs.Open strSQL, cn

Workbooks.Add
Set QT = ActiveSheet.QueryTables.Add(rs, ActiveSheet.[A1]): QT.Refresh: QT.Delete
    rs.Close
    cn.Close
End Sub

I have tested this approach, so it should works fine for you too:

Source:

enter image description here

Output:

enter image description here

Comments