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
6
Medium Priority
?
237 Views
Last Modified: 2010-04-21
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
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

by:pigmentarts
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

by:James Murrell
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 12

Author Comment

by:pigmentarts
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

by:James Murrell
ID: 21796867
yes, but note chapmandew did the hard work...
0
 
LVL 12

Author Closing Comment

by:pigmentarts
ID: 31467736
thanks both of you
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question