Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

get count

Posted on 2008-06-16
6
Medium Priority
?
240 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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