Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# get count

Posted on 2008-06-16
Medium Priority
237 Views
based on the query below, i now need to add to it and get a count of how many is in each day

example
Mon/16 (5)  <---- 5 is the count of how many Mon 16 is in the query
Sat/14 (3)

below produces

Mon/16
Sat/14

SELECT  distinct CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate
FROM orders
WHERE year(TIMEDATE)  = YEAR('#datepassed#')
AND month(TIMEDATE)  = month('#datepassed#')
ORDER BY varDate DESC
0
Question by:pigmentarts
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 60

Accepted Solution

chapmandew earned 1600 total points
ID: 21796734
SELECT  CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate, count(*)
FROM orders
WHERE year(TIMEDATE)  = YEAR('#datepassed#')
AND month(TIMEDATE)  = month('#datepassed#')
group by CONVERT(VARCHAR(10), TIMEDATE , 120)
0

LVL 12

Author Comment

ID: 21796805
what is the variable for count so i can output it?  varDate  (which i need) gives me the date not the count
0

LVL 31

Assisted Solution

James Murrell earned 400 total points
ID: 21796846
SELECT  CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate, count(*)as countnumber
FROM orders
WHERE year(TIMEDATE)  = YEAR('#datepassed#')
AND month(TIMEDATE)  = month('#datepassed#')
group by CONVERT(VARCHAR(10), TIMEDATE , 120)

I think
0

LVL 12

Author Comment

ID: 21796855
ok seems to work with the following is this ok code?

SELECT  CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate, count(order_id) as varCount
FROM orders
WHERE year(TIMEDATE)  = YEAR('#datepassed#')
AND month(TIMEDATE)  = month('#datepassed#')
group by CONVERT(VARCHAR(10), TIMEDATE , 120)
ORDER BY varDate DESC
0

LVL 31

Expert Comment

ID: 21796867
yes, but note chapmandew did the hard work...
0

LVL 12

Author Closing Comment

ID: 31467736
thanks both of you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month5 days, 11 hours left to enroll