Re: counting with reset at group by? change
From: CCA Dave (david_at_notreally.co.uk)
Date: 03/17/04
- Next message: Johnmichael Monteith: "Append One Table To Another"
- Previous message: Gopinath Munisifreddy: "How to find only updated columns in a trigger."
- In reply to: Dan Guzman: "Re: counting with reset at group by? change"
- Next in thread: CCA Dave: "Re: counting with reset at group by? change"
- Reply: CCA Dave: "Re: counting with reset at group by? change"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Mar 2004 14:30:31 -0000
Sorry, I thought the explanation was going to be simple, so I simplified the
query.
My actual query is
==========================
select Account , Product
from heads inner join lines
on heads.[PK] = lines.[FK]
order by account, product
==========================
and I want a 3rd "selection" which counts the product for the account
I don't quite follow how SQL knows what "a" is.....
Other than that, yes that's exactly what I want: thanks !
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:#ab9ecBDEHA.2768@tk2msftngp13.phx.gbl...
> > Is this possible with count(*) and group by????
>
> One method is to use a correlated subquery like the example below. GROUP
BY
> isn't needed in this case.
>
> CREATE TABLE MyTable
> (
> Head int NOT NULL,
> Line CHAR(1) NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY (Head, Line)
> )
> INSERT INTO MyTable VALUES(1, 'A')
> INSERT INTO MyTable VALUES(1, 'B')
> INSERT INTO MyTable VALUES(1, 'C')
> INSERT INTO MyTable VALUES(2, 'A')
> INSERT INTO MyTable VALUES(2, 'B')
> INSERT INTO MyTable VALUES(3, 'A')
> INSERT INTO MyTable VALUES(3, 'B')
> INSERT INTO MyTable VALUES(3, 'C')
>
> SELECT
> Head,
> Line,
> (
> SELECT COUNT(*)
> FROM MyTable AS b
> WHERE a.Head = b.Head AND
> a.Line > b.Line) + 1 AS MyCount
> FROM MyTable a
> ORDER BY
> Head,
> Line
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "CCA Dave" <david@notreally.co.uk> wrote in message
> news:c399nt$p8s$1$8300dec7@news.demon.co.uk...
> > Hi all,
> >
> > coming from a VMS powerhouse background, we used to have a count
function
> > that we could reset when a repeating value changed. Is there a way to do
> > this in SQL?
> >
> > What I want to do is:
> >
> > with data
> >
> > Head Line
> > 1 A
> > 1 B
> > 1 C
> > 2 A
> > 2 B
> > 3 A
> > 3 B
> > 3 C
> >
> > I want to count
> >
> > head line count
> >
> > 1 A 1
> > 1 B 2
> > 1 C 3
> > 2 A 1
> > 2 B 2
> > 3 A 1
> > 3 B 2
> > 3 C 3
> >
> > Is this possible with count(*) and group by????
> >
> > Thanks in advance
> >
> >
>
>
- Next message: Johnmichael Monteith: "Append One Table To Another"
- Previous message: Gopinath Munisifreddy: "How to find only updated columns in a trigger."
- In reply to: Dan Guzman: "Re: counting with reset at group by? change"
- Next in thread: CCA Dave: "Re: counting with reset at group by? change"
- Reply: CCA Dave: "Re: counting with reset at group by? change"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
