Re: How to get the 0 counts in a group by ?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 16:29:54 -0500

Peter,

In case David Portas's suggestion to add (select 0 union all select 1)
as T(ans) to the query seems a bit out of the blue:

  It sounds like you want this:

Grade 04 Reading ... 1 0*

in your result set despite the fact that your source table never has the
values Grade 04, Reading, and 1 in the same row. You generally can't
produce results that aren't in your data ;) Once you realize that you
need 0 and 1 in the output for all (col1, col2) pairs, regardless of
whether they appear in your data, you can think of adding a table
containing both 0 and 1.

SK

Peter Daniels wrote:

>My table has data in it that includes a flag value - AnsweredFlag.
>I'm having trouble when I want to group the data and report on how
>many in the group were ansered and how many were not. For example, if
>a group of data has only rows with AnsweredFLag = 0, then I get no
>returned row with a count of 0 for that group and AnsweredFLag = 1.
>I'd like top see something like:
>
><col1> <col2> ... <AnsweredFlag> <Count>
>Grade 04 Reading ... 0 35
>Grade 04 Reading ... 1 0*
>Grade 04 Writing ... 0 0*
>Grade 04 Writing ... 1 3
>...
>
>..but I can't seem to get the zero counts (*).
>
>I have been unsuccessful with "WITH ROLLUP" as well as various
>attempts at CROSS JOINS, OUTER JOINS, etc. I know this can be done
>w/o a cursor (the current implementation), but I just can't seem to go
>int th right direction.
>
>TIA,
>
>-Peter
>
>

Relevant Pages

  • Re: comparing every record in a recordset
    ... -- note the column names and the cross joins. ... -- as you loop the cursor you would concat a list of the columns. ...
    (microsoft.public.sqlserver.programming)
  • Cross Join problem
    ... This is the result of a query I have. ... there are no matching records in tbl_Salesdata for them? ... I have tried cross joins to no effect; I cannot then join in the Total so I ... end up with an identical total for Product 5, no matter which monthyear. ...
    (microsoft.public.sqlserver.programming)
  • Re: Database toolbox and MySQL
    ... With ODBC ... I get an error message when I try to do cross joins but I can retrieve the database with a simple query ...
    (comp.soft-sys.matlab)
  • Open Table Query View Only Cross Joins!!!URGENT
    ... Does anyone know why when I open a table in the enterprise manager in the ... query view I don't see the real table name I see table_1 and any query built ... that way only produces cross joins any ideas??? ...
    (microsoft.public.sqlserver.server)