using sql to make several calculations 2

[e106199]

hi all,
i had a request about creating a grid type report which will show the result of several calculation of my db fields in .NET section and a good suggestion was to use sql for this. So sql will make all calculations and i ll show the results in a grid. The problem is i dont have much experience on this and need some guidance.
Here is what i m trying to accomplish:
i have a table with say 5 columns. each column keeps 0 or 1 or <null>. the last column is the date column.
user will select a start and end date and i will find the sum off all 1's that are in the interval for each column and group them by month. And the result will be the source of my grid.
here is a sample db schema:
col1 col2 col3 col4 coldate
----- ---- ---- ---- -------
<null> 1 1 1 1/2/2005
0 0 <null> 1 1/7/2005
1 1 <null> <null> 3/1/2005

if user selects 1/1/2005 as his start date and 3/2/2005 as his end date here is what i want him to see:
month totalcol1 totalcol2 totalcol3 totalcol4
----- --------- --------- --------- ---------
jan 05 0 1 1 2
feb 05 0 0 0 0
mar 05 1 1 0 0

this will show in my grid so i have to give grid such structure.
How can i make the calculations on sql side and create such structure for the grid?
thank you
-shane

 

[e106199]

Nevermind,
i got it. i was trying a select in count, having count in select solved the problem.

its like;
select col1,
(select count(*) where col2='abc') as abcrows
....



-shane

 

[e106199]

Hi all,
i had another problem about the thread. In my last question i asked if its possible to use a second select in the first one because what i was trying to find was the count of all rows that satisfies a specific criteria. I got it working to some point but now i realized the group by date isnt working this way. If all items are 0 and 1 i can go with sum and group them by date but when i use the second select to find the number of the items that goes with my criteria the group by wont work.
so here is whats supposed to happen:
my table:
col1 col2
---- --------
abc 1/1/2006
abd 2/1/2006
abc 3/1/2006
abb 3/2/2006
abc 3/3/2006

from this table i want to get the below:
month countof ABC countof abd
----- ----------- -----------
jan 06 1 0
feb 06 0 1
mar 06 2 0