🚀 go-pugleaf

create table squibbe
(
 AccountNum int,
 OrderType char (1),
 MonthDate varchar (12)
)
go

insert into squibbe values (1, 'A', 'April')
insert into squibbe values (1, 'B', 'May')
insert into squibbe values (1, 'B', 'May')
insert into squibbe values (2, 'B', 'April')
insert into squibbe values (2, 'B', 'May')
insert into squibbe values (2, 'C', 'May')
insert into squibbe values (3, 'C', 'May')

select AccountNum as account,
            sum(case when OrderType = 'A' then 1 else 0 end) as TotA,
            sum(case when OrderType = 'B' then 1 else 0 end) as TotB,
            sum(case when OrderType = 'C' then 1 else 0 end) as TotC,
            count(*)
from squibbe
where MonthDate = 'May'
group by AccountNum
order by AccountNum

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

<u95886230@spawnkill.ip-mobilphone.net> wrote in message
news:l.989779505.1960693359@[212.171.170.132]...
> Please help, I am trying to write an SQL statement but with no success as
I am just
> starting out with sql.
>
> I have a table with 3 columns: Account# ,OrderType and date
> example of data:
> Account#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; May
>
>
> I need to write a select that will show me the totals of EACH type for
EACH account AND
> total ordersplaced for a SPECIFIC month eg..Show me the results for May...
>
> account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
>
> I can use temp tables, but need a solution written as basic as pssible so
I can understand
> it (all in the form select this from that)
> any help would be fantastic as I am completely stuck and have been trying
for about a week
>
> thanks
> sgebbie@ciaoweb.it
>
>
>
>
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new

select sum(case when ordertype='A' then 1 else 0 end) as TotA,
sum(case when ordertype='B' then 1 else 0 end) as TotB,
sum(case when ordertype='C' then 1 else 0 end) as TotC
From Thetable
where Date="May"

This works if you know how many columns(OrderTypes) that you have.  If you
do not know this query can be built dynamically.

Rob


<u95886230@spawnkill.ip-mobilphone.net> wrote in message
news:l.989779505.1960693359@[212.171.170.132]...
> Please help, I am trying to write an SQL statement but with no success as
I am just
> starting out with sql.
>
> I have a table with 3 columns: Account# ,OrderType and date
> example of data:
> Account#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; May
>
>
> I need to write a select that will show me the totals of EACH type for
EACH account AND
> total ordersplaced for a SPECIFIC month eg..Show me the results for May...
>
> account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
>
> I can use temp tables, but need a solution written as basic as pssible so
I can understand
> it (all in the form select this from that)
> any help would be fantastic as I am completely stuck and have been trying
for about a week
>
> thanks
> sgebbie@ciaoweb.it
>
>
>
>
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new