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 o
f
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...
>|||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...[vbcol=seagreen]
> Hi Itzik,
>
> Any number you can give us to play the lottery today :-))?
>
> AMB
>
> "Itzik Ben-Gan" wrote:
>|||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...
>|||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 messag
e
> news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>|||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...
>|||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...
>