Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Count grouped fields in multiple tables

    Hi Everyone,

    I have two tables which both have date columns. What I want is one single query that will return the counts of the number of records in each table for each week. I can do this for one table with this

    SELECT to_char(date_trunc('week', t.datestamp), 'YYYY-MM-DD') as "week", count(t.*) as "Surveys"
    FROM table1 t
    GROUP BY "week"
    ORDER BY "week"

    How can I add on the count for the grouped weeks (which will be different dates and some weeks may have zero records) in table 2?

    Thanks,

    Colin

  2. #2
    Join Date
    May 2009
    Posts
    7
    You could pull the two tables together using a UNION so they look as one table, then use the same SQL syntax. That's assuming you want counts on them both as one table, I may have read your post wrong.

    Cheers, Kevin.

  3. #3
    Join Date
    May 2009
    Posts
    2
    I need the counts to be separate columns in the result set rather than one

    something like

    week1 table1count, table2count
    week2 table1count, table2count

    I guess I'm trying left outer join table1 on table2 using the grouped by date column. Which doesnt seem to be possible. If UNION will append the rows of table 1to the bottom of table 1 and count them together, i need something that will give one count of the rows for each week in table1 and one count of the rows for each week in table 2.

    seems like it should be possible, i actually have 5 tables i ultimately want counts of rows by week, month etc for.

    Colin

  4. #4
    Join Date
    May 2009
    Posts
    7
    Hi, you could probably do a select count on all the tables and then do a union. i.e. something like

    select count(1) as a, 0 as b, 0 as c from x
    union
    select 0 as a, count(1) as b, 0 as c from y
    untion
    select 0 as a, 0 as b, count(1) as c from z

    and then do a query around it.... something like

    select max(a) as a, max(b) as b, max(c) as c aroiund that query

    HTH

    Kevin.

  5. #5
    Join Date
    Jun 2009
    Posts
    7
    Make a new table for each table with your original query,
    then put them together with some join. I'm thinking that's pretty much the only way of doing it if you want a column per table.
    Something like (Q1 is your original query on first table)
    SELECT q1.ts,q1.count,q2.count, ...
    FROM (Q1 as q1) INNER JOIN (Q2 as q2) ON q1.ts = q2.ts
    ORDER BY q1.ts (probably not needed as you already order them in Q)
    Also, the inner join should be replaced if it's possible for a table to not hold a value for a certain week.

Posting Permissions

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