SQL query help...

SQL query help...

Post by omavlan » Tue, 21 Oct 2003 12:18:40


Hi,

I have writtem the query....

select
sum(
case when shmsharect >= 10000 then 1
else 0
end
) as cnt_big
,
sum(
case when shmsharect >= 10000 then shmsharect
else 0
end
) as shares_big
, sum(
case when shmsharect < 10000 then 1
else 0
end
) as cnt_small
, sum(
case when shmsharect < 10000 then shmsharect
else 0
end
) as shares_small
,
shmctryres
from shrsharemaster
where shmctryinc = ''
group by shmctryres


The above query returns the output as

cnt_big shares_big cnt_small shares_small shmctryres

0 0 3 3000 China
1 10000 2 0 Indonesia
0 0 1 0 India
2 22000 1 4000 Japan
0 0 4 0 Malaysia
1 27000 2 8000 Singapore


I would like to add two colums to calculate the count.

One column as col1 to calculate sum cnt_big + cnt_small for each record

Another column as col2 to calculate sum shares_big + shares_small for each record.

I need the output like below...

cnt_big shares_big cnt_small shares_small shmctryres col1 col2

0 0 3 3000 China 3 3000
1 10000 2 0 Indonesia 3 10000
0 0 1 0 India 1 0
2 22000 1 4000 Japan 3 26000
0 0 4 0 Malaysia 4 0
1 27000 2 8000 Singapore 3 35000


Pls help..

Regards,
Omav
 
 
 

1. SQL Query Help?

2. sql query help

This is my first encounter w/ coding a sql update query.

Code:
================================
Option Compare Database
Option Explicit

Function UpdateFiscalCalendarDates()

Dim reportdate As Date
reportdate = DateAdd("d", -7, Date)
Dim strSQL As String

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=[reportdate]));"

Debug.Print strSQL
DoCmd.RunSQL strSQL

End Function

==================================

i am prompted for the reportdate. Can someone help me with the code?

thanks.
patti

3. SQL query help

4. sql query help needed

5. SQL query help!

6. SQL Query...HELP REQUIRED!

7. SQL Query help !!

8. SQL Query Help

9. sql query- help!

10. SQL Query Help-- Order By Clause

11. SQL query help needed I think

12. SQL Query Help Needed

13. SQL Query Help Please

14. sql query help! COUNT function

15. SQL query help needed