Professional Web Applications Themes

What a pig's ear I've made of my earlier post - MySQL

Hmm, perhaps I didn't explain myself too well on the problem I'm having with my database in my last post over a week ago. There's a table, which contains a timestamp field, amongst others. How would I write a query that does the following: 1. For each year, count all the records for that given year. There's approximately 6 years' worth. 2. Within each year, for each month, count all the records that matches the month (i.e. Feburary 2006). How would I go about achieving that? -- [url]http://www.munted.org.uk[/url] Take a nap, it saves lives....

  1. #1

    Default What a pig's ear I've made of my earlier post

    Hmm, perhaps I didn't explain myself too well on the problem I'm having
    with my database in my last post over a week ago. There's a table, which
    contains a timestamp field, amongst others. How would I write a query
    that does the following:

    1. For each year, count all the records for that given year. There's
    approximately 6 years' worth.
    2. Within each year, for each month, count all the records that
    matches the month (i.e. Feburary 2006).

    How would I go about achieving that?
    --
    [url]http://www.munted.org.uk[/url]

    Take a nap, it saves lives.
    Alex Buell Guest

  2. #2

    Default Re: What a pig's ear I've made of my earlier post

    Alex Buell wrote:
    > Hmm, perhaps I didn't explain myself too well on the problem I'm
    > having with my database in my last post over a week ago. There's a
    > table, which contains a timestamp field, amongst others. How would I
    > write a query that does the following:
    >
    > 1. For each year, count all the records for that given year. There's
    > approximately 6 years' worth.
    > 2. Within each year, for each month, count all the records that
    > matches the month (i.e. Feburary 2006).
    >
    > How would I go about achieving that?
    For the year totals you can do:
    SELECT year( entry ) , count( * )
    FROM `guestbook`
    GROUP BY year( entry )

    For the month totals you can do:

    SELECT concat_ws( '-', year( entry ) , month( entry ) ) , count( * )
    FROM `guestbook`
    GROUP BY concat_ws( '-', year( entry ) , month( entry ) ) ))

    or

    SELECT left( entry, 7 ) , count( * )
    FROM `guestbook`
    GROUP BY left( entry, 7 )


    Paul Lautman Guest

  3. #3

    Default Re: What a pig's ear I've made of my earlier post

    See revised month query below, dunno where the extra brackets came from???

    Paul Lautman wrote:
    > Alex Buell wrote:
    >> Hmm, perhaps I didn't explain myself too well on the problem I'm
    >> having with my database in my last post over a week ago. There's a
    >> table, which contains a timestamp field, amongst others. How would I
    >> write a query that does the following:
    >>
    >> 1. For each year, count all the records for that given year. There's
    >> approximately 6 years' worth.
    >> 2. Within each year, for each month, count all the records that
    >> matches the month (i.e. Feburary 2006).
    >>
    >> How would I go about achieving that?
    >
    > For the year totals you can do:
    > SELECT year( entry ) , count( * )
    > FROM `guestbook`
    > GROUP BY year( entry )
    >
    > For the month totals you can do:
    >
    SELECT concat_ws( '-', year( entry ) , month( entry ) ) , count( * )
    FROM `guestbook`
    GROUP BY concat_ws( '-', year( entry ) , month( entry ) )
    >
    > or
    >
    > SELECT left( entry, 7 ) , count( * )
    > FROM `guestbook`
    > GROUP BY left( entry, 7 )


    Paul Lautman Guest

  4. #4

    Default Re: What a pig's ear I've made of my earlier post

    On Fri, 26 May 2006 21:24:30 +0100, I waved a wand and this message
    magically appeared:
    > See revised month query below, dunno where the extra brackets came
    > from???
    Goolegroups? ;o)

    Anyway, thanks for the help. It's reduced the network traffic
    considerably, which is a big plus!

    Thanks!
    --
    [url]http://www.munted.org.uk[/url]

    Take a nap, it saves lives.
    Alex Buell Guest

  5. #5

    Default Re: What a pig's ear I've made of my earlier post

    On Fri, 26 May 2006 21:24:30 +0100, "Paul Lautman"
    <paul.lautmanbtinternet.com> wrote:
    >SELECT concat_ws( '-', year( entry ) , month( entry ) ) , count( * )
    >FROM `guestbook`
    >GROUP BY concat_ws( '-', year( entry ) , month( entry ) )
    If the OP modifies this to

    SELECT concat_ws( '-', year( entry ) , month( entry ) ) , year(entry),
    count( * )
    FROM `guestbook`
    GROUP BY year(entry), concat_ws( '-', year( entry ) , month( entry) )
    WITH ROLLUP;

    he will have the entries summed up within each year.

    --
    Pawel
    PFG Guest

  6. #6

    Default Re: What a pig's ear I've made of my earlier post

    PFG wrote:
    > On Fri, 26 May 2006 21:24:30 +0100, "Paul Lautman"
    > <paul.lautmanbtinternet.com> wrote:
    >
    >> SELECT concat_ws( '-', year( entry ) , month( entry ) ) , count( * )
    >> FROM `guestbook`
    >> GROUP BY concat_ws( '-', year( entry ) , month( entry ) )
    >
    > If the OP modifies this to
    >
    > SELECT concat_ws( '-', year( entry ) , month( entry ) ) , year(entry),
    > count( * )
    > FROM `guestbook`
    > GROUP BY year(entry), concat_ws( '-', year( entry ) , month( entry) )
    > WITH ROLLUP;
    >
    > he will have the entries summed up within each year.
    Neat


    Paul Lautman Guest

Similar Threads

  1. Earlier versions available?
    By MaryO26 in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: May 15th, 12:18 AM
  2. CS can't open AI10 files saved as earlier versions (9 and earlier)
    By kurt_triffet@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 0
    Last Post: February 27th, 12:04 AM
  3. ASP DATE between NOW and 24 HOURS earlier.
    By Darren in forum ASP Components
    Replies: 11
    Last Post: January 26th, 09:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139