If a certain date = Monday?, how to do this 3

[NFLDUser]

I have a table with each row having a date field. I am trying to return the total number of rows from each day of the week.

Mon Tues Wed Thurs Fri Sat Sun
44 45 3 45 23 2 34

I'd like to have something like the above to work with.

Any ideas????

 

[lespaul]

what does the data look like in the table that you are trying to convert to this structure?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[NFLDUser]

The date is in the table as a Date/Time field as Short Date.

Hope this helps!

So basically, I want to count every row that has a date from Monday...Tuesday...Wednesday, and so on.

 

[lespaul]

SELECT WeekDay([SomeDateField]), Count(*) FROM TableName GROUP BY WeekDay([SomeDateField])

will return 1 - 7 and a count. There may be a function that returns the name of the day, but if not, you could use an if statements to get the names. Then you can pivot these results to get the format you need.

 

[Golom]

Select WeekDay([DateField]) As [Week Day],
       Count(*) As [CountOfWeekDay]

From myTable

Group By WeekDay([DateField])

That will give you
[tt]
Week Day CountOFWeekDay
1 10
2 15
3 9
etc.
[/tt]
Where 1=Sunday, 2=Monday, etc.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

Awesome...thanks guys!!!

 

[NFLDUser]

One follow-up though...

So now I have the totals for each day, but what if I wanted avg for each day of the week?

 

[Golom]

What do you mean by "Average"?

Normally, "average" is computed as the mean of a set of observations (e.g. Sum / number of observations). Do you perhaps mean "% of total"?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

Well, each row in my table represents an invoice, with a date field.

I am trying to figure out how many ppl purchase on Mon-Sun.

So if I have 20 invoices for Monday and the invoices are for 4 Mondays, then my average would be 5.

I guess somewhere in the SQL, I need to keep track of how many different dates there are. Then, TotalForDay / TotalDaysForEachDayOfWeek ... or something like that.

Hope I am making sense.

 

[Golom]

Try

Select WeekDay(M.[DateField]) As [Week Day],
       Count(*) As [CountOfWeekDay],

       (Count(*) / 
       (SELECT Count(*) FROM myTable AS X
        WHERE Weekday(X.[DateField])=WeekDay(M.[DateField]) 
        GROUP BY CDate(Int(X.[DateField]) ) As [AverageValue]

From myTable M

Group By WeekDay(M.[DateField])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

I keep getting an error. It's probably something I am overlooking here.

'Missing ], ), or Item in query expression '(Count(*) /
(SELECT Count(*) FROM Breakdown AS X
WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
GROUP BY CDate(Int(X.[visit_date]) ) As [AverageValue]

From Breakdown M

Group By WeekDay(M.[visit_date])'.


Breakdown is my table.
visit_date is my date field.

 

[lespaul]

Select WeekDay(M.[DateField]) As [Week Day],
Count(*) As [CountOfWeekDay],

(Count(*) /
(SELECT Count(*) FROM myTable AS X
WHERE Weekday(X.[DateField])=WeekDay(M.[DateField])
GROUP BY CDate(Int(X.[DateField]) ) As [AverageValue])

From myTable M

Group By WeekDay(M.[DateField])

 

[Golom]

Sorry. You need more right parens

'(Count(*) /
       (SELECT Count(*) FROM Breakdown AS X
        WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
        GROUP BY CDate(Int(X.[visit_date]))[COLOR=red yellow]))[/color] As [AverageValue]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

One more error now.

'At most, one query can be returned by this subquery.'

Thanks!

 

[Golom]

Guess it needs to be

(Count(*) /

(SELECT Count(*) 
 FROM 
      (SELECT DISTINCT X.[visit_date]
       FROM Breakdown AS X
       WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
      ) As J) As [AverageValue]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

Not sure whyk, but its still causing problems.

'Syntax error in union query.'

 

[lespaul]

what's the exact SQL you are trying? There is no UNION in what's been suggested!!

 

[NFLDUser]

(Count(*) /

(SELECT Count(*)
FROM
(SELECT DISTINCT X.[visit_date]
FROM Breakdown AS X
WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
) As J) As [AverageValue]

I assumed that this was the complete SQL. This is where I get the union error.

When I try the following, I get a syntax erro.

Select WeekDay(M.[DateField]) As [Week Day],
Count(*) As [CountOfWeekDay],

(Count(*) /

(SELECT Count(*)
FROM
(SELECT DISTINCT X.[visit_date]
FROM Breakdown AS X
WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
) As J) As [AverageValue]

From myTable M

Group By WeekDay(M.[DateField])


My apologizes for not being able to pick at it to get this right. It's been a few years since I played with SQL nd I'm just stumped here!!

 

[NFLDUser]

Is this making sense to anyone?

 

[Golom]

Just another missing paren

Select WeekDay(M.[DateField]) As [Week Day],
       Count(*) As [CountOfWeekDay],

(Count(*) /

(SELECT Count(*)
 FROM
      (SELECT DISTINCT X.[visit_date]
       FROM Breakdown AS X
       WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
      ) As J)[b][COLOR=red])[/color][/b]  As [AverageValue]

From [COLOR=blue]Breakdown [/color]As M

Group By WeekDay(M.[DateField])

As Leslie said, there's no UNION in this statement.

I've also changed "myTable" to Breakdown since I assume that's the correct name for your table.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]