[UPHPU] OT: SQL question (MSSQL)

[Tyler Gee]

I think with all the grouping you have going on you are going to need
to do it with a subquery.  Basically, just write a select statement
that is getting the count values you want and then replace the sum()
part in your statement with that.  Something like:

#
SELECT  'ChargeCode' = vpb900.chg_cod_ext_id,
        [SNIP]
        'Count' = (select statement here)
FROM    VPB900_CHG_CODE_MST vpb900 (nolock),
        [SNIP]



On 9/6/06, J M <jmul.php at gmail.com> wrote:
> If you look at my first example of how it is coming out, replace the number
> 24052 with 9398 on every record.  That is what happens when I use the word
> count().
>
> Books Online says count() = Returns the number of items in a group.  I've
> run other reports and I believe 24052 is what the count column should add up
> to.
>
>
>
> On 9/6/06, Tyler Gee <geekout at gmail.com> wrote:
> > What happens if you use count() instead of sum()?
> >
> > On 9/6/06, J M <jmul.php at gmail.com> wrote:
> > > Sorry, I was going to post it but thought you might know what I was
> talking
> > > about conceptually...assuming I gave a decent explanation.
> > >
> > > http://paste-bin.com/336
> > >
> > > Note: When I uncomment the last 'group by' line it lists all charge
> codes on
> > > multiple lines.
> > >
> > >
> > >
> > > On 9/6/06, Tyler Gee <geekout at gmail.com> wrote:
> > > > Post your SQL statement.
> > > >
> > > > On 9/6/06, J M < jmul.php at gmail.com> wrote:
> > > > > I have a SQL question.  Please let me know if it is too far off
> topic
> > > and
> > > > > I'll take this question elsewhere.  I have to work with MSSQL but I
> > > think I
> > > > > might be able to translate, if I have to, your suggestions based on
> your
> > > > > knowledge of other rdbms's to make it work with MSSQL.  I'm looking
> > > around
> > > > > for an MSSQL mailing group to join that doesn't dive too far into
> .Net
> > > > > stuff.
> > > > >
> > > > > I asked this question on the IRC channel but I'm still having a
> problem.
> > >  I'm
> > > > > trying to run a query that will return a charge_code and a count of
> how
> > > many
> > > > > times this charge code was used.  I'm also getting other data back
> but
> > > my
> > > > > question has to do with the charge code and the count.
> > > > >
> > > > > I've rearranged the code and tried all the ideas I could think of
> and
> > > I'm
> > > > > still not getting the desired result.  I want all 6300+ charge codes
> > > listed
> > > > > in my result whether or not they were used.  If they weren't used I
> want
> > > a
> > > > > zero in the count column.  What is happening is every single record
> > > contains
> > > > > the total number of all the charge codes used in the time frame
> > > specified.
> > > > >
> > > > > Example of what I'm getting…
> > > > >
> > > > > chg_code         count
> > > > >
> > > > > 0060N             24052
> > > > >
> > > > > 0061N             24052
> > > > >
> > > > > 00961              24052
> > > > >
> > > > > 0552N             24052
> > > > >
> > > > > …
> > > > >
> > > > > Example of what I'd like to get…
> > > > >
> > > > > chg_code         count
> > > > >
> > > > > 0060N             15
> > > > >
> > > > > 0061N             259
> > > > >
> > > > > 00961              0
> > > > >
> > > > > 0052N             72
> > > > >
> > > > > …
> > > > >
> > > > > Would I need a subquery with some sort of loop that adds up the
> count?
> > > If
> > > > > so, I've never done loops in SQL.  Are there any good web
> references?
> > > (Books
> > > > > Online isn't helping me any)  If using a loop is too complicated for
> > > this
> > > > > problem, is there another (better) way to approach this?
> > > > > TIA,
> > > > >
> > > > > John
> > > > >
> > > > > _______________________________________________
> > > > >
> > > > > UPHPU mailing list
> > > > > UPHPU at uphpu.org
> > > > > http://uphpu.org/mailman/listinfo/uphpu
> > > > > IRC: #uphpu on irc.freenode.net
> > > > >
> > > >
> > > >
> > > > --
> > > > ~Tyler
> > > >
> > >
> > >
> >
> >
> > --
> > ~Tyler
> >
>
>


-- 
~Tyler