4 SQL Questions

From the past questions I post, I am able to use them individually but when I try to use them together, I can't get it done.

The layout in my Table1: there are 6 fields, Value1 - Value5 and a date field. Value1 - Value6 only contains 1 or 0. Date has a default value = getdate().

In order to filter by date, i used:
SELECT *
FROM Table1
WHERE (Date BETWEEN '10-feb-02' AND '10-feb-02 23:59:59')

In order to get the total record of 1s which Value1 has, i used:
SELECT Count(Value1) FROM Table1
WHERE Value1 = '1'

Question 1:
However currently i need to view the total of 1s of Value1 - Value6 respectively with the date filter together. So what is the SQL Statement?

Question2:
So in Question 1 i will be able to view 6 total counts filter by date. I would also like to sort them out and view only the top 3 count. So what is the SQL Statement?

Question 3:
I would also like to have the sum of Question 1 result. That mean I should have the total 1s that is in Value1 - Value5 with date filter.

Question 4:
Is there any way to export Question 1-3 results into Excel format automatically in MS SQL. Cos I know I can set weekly timer to export all the record of the entire database into excel file but i do not know how to customize the result i want to be exported.

Thanks a million.

in question 1 it looks like you want to see all the counts of the six fields. seems like you may need a union.

this will give you the counts of the first 3 fields by date.
these will be distinct by the datefield. remember to format the datefield to remove time portion or else all the records will be distinct(all your counts will be 1).

try using a sub-query like this.

select sub.value1count, sub.value2count, sub.value3count, sub.value4count,sub.value5count,sub.value6count
FROM
(SELECT Count(Value1) as value1count,0 as value2count,0 as value3count ,0 as value4count,0 as value5count,0 as value6count,datefield FROM Table1
WHERE Value1 = '1'
group by datefield
UNION ALL
SELECT 0,Count(Value2),0,0,0,0, datefield FROM Table1
WHERE Value1 = '1'
group by datefield
UNION ALL
SELECT 0,0,Count(Value3),0,0,0,datefield FROM Table1
WHERE Value1 = '1'
group by datefield) as sub

you can filter off of this subquery for top3 and max(datefield)

letme know if you still have trouble.

What do u mean by datefield? And what is distinct?
Yes i wanted to see all the count for 5 fields but where do i add in
WHERE (Date BETWEEN '10-feb-02' AND '10-feb-02 23:59:59')?
By the way i no need to count the date field, the date is acts as the range for me to filter.


And for Question 2, I think it is my clear enough. I need to see the top 3 counts out of the 5 fields. So if Value1='100', Value2='101', Value3='23', Value4='200', Value5='300'. Then it will show Value2='101', Value4='200', Value5='300'.