Complex counting, summing and grouping

I have a table of Sales statistics as follows, with each record bearing
invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
the salesRep and the SalesAmount the SalesRep contributed
Create Table
sales (invoiceID INT NOT NULL,
salesRepID INT NOT NULL,
SalesDept VARCHAR(5) NOT NULL,
salesAmount DECIMAL NOT NULL)
Each InvoiceID can contain contribution of multiple sales reps in multiple
sales department, each with a different sales amount contribution with the
following sample Data:
INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
from an earlier period
INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
A minus is denoted as a "refund".
I want to produce a query on the *number* (i.e. not amount) of sales
generated by each department, as follows
SalesDept CountPerDept
QW9 0.5
QE9 0.33333
S71 -0.66667
S73 1.33333
What is the simplest SQL that can be used to produce the above output?Patrick,
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
Can you explain where are those numbers coming from?
AMB
"Patrick" wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||This is a wild guess in terms of what you meant; the result matches
though...
-- 2005
with c as
(
select
salesdept,
1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
from sales
)
select salesdept, sum(cnt) cntperdept
from c
group by salesdept;
-- 2000
select salesdept, sum(cnt) cntperdept
from
(
select
salesdept,
1.*sign(salesamount) /
(select count(*) from sales as s2
where s2.invoiceid = s1.invoiceid) as cnt
from sales as s1
) as d
group by salesdept;
A clearer picture of what you're after would allow less guess work...
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||Hi Itzik,
> This is a wild guess
Any number you can give us to play the lottery today :-))?
AMB
"Itzik Ben-Gan" wrote:
> This is a wild guess in terms of what you meant; the result matches
> though...
> -- 2005
> with c as
> (
> select
> salesdept,
> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> from sales
> )
> select salesdept, sum(cnt) cntperdept
> from c
> group by salesdept;
> -- 2000
> select salesdept, sum(cnt) cntperdept
> from
> (
> select
> salesdept,
> 1.*sign(salesamount) /
> (select count(*) from sales as s2
> where s2.invoiceid = s1.invoiceid) as cnt
> from sales as s1
> ) as d
> group by salesdept;
> A clearer picture of what you're after would allow less guess work...
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
> >I have a table of Sales statistics as follows, with each record bearing
> > invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> > of
> > the salesRep and the SalesAmount the SalesRep contributed
> >
> > Create Table
> > sales (invoiceID INT NOT NULL,
> > salesRepID INT NOT NULL,
> > SalesDept VARCHAR(5) NOT NULL,
> > salesAmount DECIMAL NOT NULL)
> >
> > Each InvoiceID can contain contribution of multiple sales reps in multiple
> > sales department, each with a different sales amount contribution with the
> > following sample Data:
> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> > from an earlier period
> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> > A minus is denoted as a "refund".
> >
> > I want to produce a query on the *number* (i.e. not amount) of sales
> > generated by each department, as follows
> >
> > SalesDept CountPerDept
> > QW9 0.5
> > QE9 0.33333
> > S71 -0.66667
> > S73 1.33333
> >
> > What is the simplest SQL that can be used to produce the above output?
>|||Sure,
with
l0 as (select 0 as c union all select 0),
l1 as (select 0 as c from l0 as a, l0 as b),
l2 as (select 0 as c from l1 as a, l1 as b),
l3 as (select 0 as c from l2 as a, l2 as b),
nums as (select top(49) row_number() over(order by c) as n from l3),
choice as (select top(6) n from nums order by checksum(newid()))
select stuff(
(select ',' + cast(n as varchar(10)) as [text()]
from choice order by n for xml path('')), 1, 1, '');
;-)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
> Hi Itzik,
>> This is a wild guess
> Any number you can give us to play the lottery today :-))?
>
> AMB
>
> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>|||On May 16, 11:25 pm, Patrick <p...@.reply.newsgroup.msn.com> wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?
Do you consider a negative amount as sales? Your first insert...
If yes then following:
select salesdept,count(*)
from sales
where salesamount > 0
group by salesdept
else...
select salesdept,count(*)
from sales
group by salesdept|||Itzik, you are such a http://en.wikipedia.org/wiki/Brainiac
;)
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||Itzik,
LOL :-))))
Both are good ones.
Best wishes,
AMB
"Itzik Ben-Gan" wrote:
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
> > Hi Itzik,
> >
> >> This is a wild guess
> >
> > Any number you can give us to play the lottery today :-))?
> >
> >
> > AMB
> >
> >
> > "Itzik Ben-Gan" wrote:
> >
> >> This is a wild guess in terms of what you meant; the result matches
> >> though...
> >>
> >> -- 2005
> >> with c as
> >> (
> >> select
> >> salesdept,
> >> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> >> from sales
> >> )
> >> select salesdept, sum(cnt) cntperdept
> >> from c
> >> group by salesdept;
> >>
> >> -- 2000
> >> select salesdept, sum(cnt) cntperdept
> >> from
> >> (
> >> select
> >> salesdept,
> >> 1.*sign(salesamount) /
> >> (select count(*) from sales as s2
> >> where s2.invoiceid = s1.invoiceid) as cnt
> >> from sales as s1
> >> ) as d
> >> group by salesdept;
> >>
> >> A clearer picture of what you're after would allow less guess work...
> >>
> >> Cheers,
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >> www.insidetsql.com
> >>
> >>
> >> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> >> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
> >> >I have a table of Sales statistics as follows, with each record bearing
> >> > invoiceID, salesRep who contributed towards the sales, the
> >> > SalesDepartment
> >> > of
> >> > the salesRep and the SalesAmount the SalesRep contributed
> >> >
> >> > Create Table
> >> > sales (invoiceID INT NOT NULL,
> >> > salesRepID INT NOT NULL,
> >> > SalesDept VARCHAR(5) NOT NULL,
> >> > salesAmount DECIMAL NOT NULL)
> >> >
> >> > Each InvoiceID can contain contribution of multiple sales reps in
> >> > multiple
> >> > sales department, each with a different sales amount contribution with
> >> > the
> >> > following sample Data:
> >> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
> >> > refund
> >> > from an earlier period
> >> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> >> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> >> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> >> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> >> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> >> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> >> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> >> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> >> > A minus is denoted as a "refund".
> >> >
> >> > I want to produce a query on the *number* (i.e. not amount) of sales
> >> > generated by each department, as follows
> >> >
> >> > SalesDept CountPerDept
> >> > QW9 0.5
> >> > QE9 0.33333
> >> > S71 -0.66667
> >> > S73 1.33333
> >> >
> >> > What is the simplest SQL that can be used to produce the above output?
> >>
> >>
>|||That is one of the most creative yet powerfully useless tsql statements I
have ever seen!!
Now, if I could just figure out how the hell it works . . . LOL
--
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||HEY, wait a minute!! In non-scientific testing I have deterimined that the
output is weighted low-to-high from left to right.
I want my money back! :-))
Still chuckling about the code tho!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||One is reminded of the student in the movie 'PCU' that will
gladly pay for a term paper. He is reminded that he is basing
his academic career on a dead language some 5000 years old
when it is revealed that his subject is Sanskrit. And he is
quickly dismissed as no amount of money can overcome the
absurdity of the request. Hopefully, someday, the parallel
will dawn on you.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>