Sql Comparing two tables: grouping

Showing posts with label grouping. Show all posts

Thursday, March 29, 2012

Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sql sql

Tuesday, March 27, 2012

Concat instead of SUM when grouping results

Hello,

I have a very simple problem which I will illustrate with an example:

I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K

I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K

So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?

Kind regards,

Bart WarnezHi Bart,

I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:

CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))

INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'

SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3

DROP TABLE test

which seems to work :)

Good luck!
J|||On 23 nov, 12:52, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
>
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
>
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
>
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
>
DROP TABLE test
>
which seems to work :)
>
Good luck!
J

Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.

Kind Regards,

Bart|||I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...

create function dbo.fn_groupIt(@.test1 varchar(5),@.test3 varchar(5))
returns varchar(5000)
as
begin
declare @.out varchar(5000)
select@.out = coalesce(@.out + ',' + convert(varchar,test2),
convert(varchar,test2))
fromtest
wheretest1 = @.test1 and
test3 = @.test3

return @.out
end

selecttest1, dbo.fn_groupIt(test1,test3) test2,test3
from(
selecttest1,test3
fromtest
group by test1,test3
) a|||Hi Bart,

What spec server are you using? I can run either script in under a
second :-/

J|||On 23 nov, 15:46, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
What spec server are you using? I can run either script in under a
second :-/
>
J

Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!

Bart|||>I guess there is no function to do this... What is the simplest solution? <<

Do it in the front end instead violating 1NF in the Database side.|||On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest solution? <<

>
Do it in the front end instead violating 1NF in the Database side.

Hi,

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.

Kind regards,

Bart|||"Bart op de grote markt" <warnezb@.googlemail.comwrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I guess there is no function to do this... What is the simplest
>solution? <<

>>
>Do it in the front end instead violating 1NF in the Database side.

>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

You're "wrong".

You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.

If you design your database properly, your queries follow from that.

Quote:

Originally Posted by

>
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Quote:

Originally Posted by

>
Kind regards,
>
Bart

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.

This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?

J|||On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"Bart op de grote markt" <warn...@.googlemail.comwrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
>

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest
solution? <<

Quote:

Originally Posted by

Quote:

Originally Posted by

Do it in the front end instead violating 1NF in the Database side.

Quote:

Originally Posted by

Hi,

Quote:

Originally Posted by

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.

I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.

And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )

Quote:

Originally Posted by

Quote:

Originally Posted by

>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>

As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.

Kind regards and thx for all your comments

Bart|||You're "wrong".

Actually Greg - You're "wrong".

SQL Server is a data engine and not just a relational data storage method.

There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.

For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.

There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...

--
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]

Friday, February 24, 2012

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?
>>
>

Complex counting, summing and grouping

Complex counting, joining and grouping

I have a sales stats table and I want to do some counting and grouping by
source and cluster (join required).
The 2 tables and sample data are as follows:
CREATE TABLE
dbo.stat (id_source varchar(10) not null,
id_period int not null,
id_dept varchar(10) not null,
ind_Domestic char(1) not null,
trade_count int not null)
CREATE TABLE dbo.stat_Hierarchy(id_dept varchar(5) not null,
id_cluster varchar(10) not null)
INSERT INTO stat VALUES('INTERNET',200601,'N41','Y',250)
INSERT INTO stat VALUES('INTERNET',200601,'N41','N',100)
INSERT INTO stat VALUES('INTERNET',200601,'S51','Y',200)
INSERT INTO stat VALUES('INTERNET',200601,'S51','N',120)
INSERT INTO stat VALUES('INTERNET',200601,'021','Y',50)
INSERT INTO stat VALUES('INTERNET',200601,'021','N',70)
INSERT INTO stat VALUES('INTERNET',200601,'131','Y',30)
INSERT INTO stat VALUES('INTERNET',200601,'131','N',70)
INSERT INTO stat VALUES('STORE',200601,'00P','Y',130)
INSERT INTO stat VALUES('STORE',200601,'00P','N',1)
INSERT INTO stat VALUES('STORE',200601,'00S','N',100)
INSERT INTO stat VALUES('STORE',200601,'N41','Y',130)
INSERT INTO stat VALUES('STORE',200601,'N41','N',250)
INSERT INTO stat VALUES('STORE',200601,'S51','Y',110)
INSERT INTO stat VALUES('STORE',200601,'S51','N',320)
INSERT INTO stat VALUES('STORE',200601,'021','Y',30)
INSERT INTO stat VALUES('STORE',200601,'021','N',40)
INSERT INTO stat VALUES('AGENCY',200601,'0101','Y',50)
INSERT INTO stat VALUES('AGENCY',200601,'0101','N',10)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','Y',100
)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','N',320
)
INSERT INTO stat VALUES('AGENCY',200601,'021','Y',150)
INSERT INTO stat VALUES('AGENCY',200601,'021','N',50)
INSERT INTO stat VALUES('AGENCY',200601,'131','Y',20)
INSERT INTO stat VALUES('AGENCY',200601,'131','N',80)
INSERT INTO stat_Hierarchy VALUES('00P','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('00S','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('0101','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('021','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('131','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('0100300','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('N41','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('S51','HV_CL_SALE')
And I want the data to be returned like so:
id_cluster,id_source,ind_Domestic,trade_
count
HV_CL_SALE,INTERNET,N,220
HV_CL_SALE,INTERNET,Y,450
HV_CL_SALE,STORE,N,570
HV_CL_SALE,STORE,Y,240
IB_CL_OTHE,STORE,N,101
IB_CL_OTHE,STORE,Y,130
LV_CL_SALE,AGENCY,N,140
LV_CL_SALE,AGENCY,Y,200
LV_CL_SALE,INTERNET,N,140
LV_CL_SALE,INTERNET,Y,80
LV_CL_SALE,STORE,N,40
LV_CL_SALE,STORE,Y,30
How could I do this?select
h.id_cluster,
s.id_source,
s.ind_Domestic,
sum(s.trade_count) as trade_count
from stat s
inner join stat_Hierarchy h on h.id_dept=s.id_dept
group by h.id_cluster,s.id_source,s.ind_Domestic
order by h.id_cluster,s.id_source,s.ind_Domestic

Complex counting, joining and grouping

I have a sales stats table and I want to do some counting and grouping by
source and cluster (join required).
The 2 tables and sample data are as follows:
CREATE TABLE
dbo.stat (id_source varchar(10) not null,
id_period int not null,
id_dept varchar(10) not null,
ind_Domestic char(1) not null,
trade_count int not null)
CREATE TABLE dbo.stat_Hierarchy(id_dept varchar(5) not null,
id_cluster varchar(10) not null)
INSERT INTO stat VALUES('INTERNET',200601,'N41','Y',250)
INSERT INTO stat VALUES('INTERNET',200601,'N41','N',100)
INSERT INTO stat VALUES('INTERNET',200601,'S51','Y',200)
INSERT INTO stat VALUES('INTERNET',200601,'S51','N',120)
INSERT INTO stat VALUES('INTERNET',200601,'021','Y',50)
INSERT INTO stat VALUES('INTERNET',200601,'021','N',70)
INSERT INTO stat VALUES('INTERNET',200601,'131','Y',30)
INSERT INTO stat VALUES('INTERNET',200601,'131','N',70)
INSERT INTO stat VALUES('STORE',200601,'00P','Y',130)
INSERT INTO stat VALUES('STORE',200601,'00P','N',1)
INSERT INTO stat VALUES('STORE',200601,'00S','N',100)
INSERT INTO stat VALUES('STORE',200601,'N41','Y',130)
INSERT INTO stat VALUES('STORE',200601,'N41','N',250)
INSERT INTO stat VALUES('STORE',200601,'S51','Y',110)
INSERT INTO stat VALUES('STORE',200601,'S51','N',320)
INSERT INTO stat VALUES('STORE',200601,'021','Y',30)
INSERT INTO stat VALUES('STORE',200601,'021','N',40)
INSERT INTO stat VALUES('AGENCY',200601,'0101','Y',50)
INSERT INTO stat VALUES('AGENCY',200601,'0101','N',10)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','Y',100)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','N',320)
INSERT INTO stat VALUES('AGENCY',200601,'021','Y',150)
INSERT INTO stat VALUES('AGENCY',200601,'021','N',50)
INSERT INTO stat VALUES('AGENCY',200601,'131','Y',20)
INSERT INTO stat VALUES('AGENCY',200601,'131','N',80)
INSERT INTO stat_Hierarchy VALUES('00P','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('00S','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('0101','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('021','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('131','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('0100300','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('N41','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('S51','HV_CL_SALE')
And I want the data to be returned like so:
id_cluster,id_source,ind_Domestic,trade_count
HV_CL_SALE,INTERNET,N,220
HV_CL_SALE,INTERNET,Y,450
HV_CL_SALE,STORE,N,570
HV_CL_SALE,STORE,Y,240
IB_CL_OTHE,STORE,N,101
IB_CL_OTHE,STORE,Y,130
LV_CL_SALE,AGENCY,N,140
LV_CL_SALE,AGENCY,Y,200
LV_CL_SALE,INTERNET,N,140
LV_CL_SALE,INTERNET,Y,80
LV_CL_SALE,STORE,N,40
LV_CL_SALE,STORE,Y,30
How could I do this?
select
h.id_cluster,
s.id_source,
s.ind_Domestic,
sum(s.trade_count) as trade_count
from stat s
inner join stat_Hierarchy h on h.id_dept=s.id_dept
group by h.id_cluster,s.id_source,s.ind_Domestic
order by h.id_cluster,s.id_source,s.ind_Domestic

Thursday, March 29, 2012

Concatenate strings in group column

Tuesday, March 27, 2012

Concat instead of SUM when grouping results

Friday, February 24, 2012

Complex counting, summing and grouping

Complex counting, summing and grouping

Complex counting, summing and grouping

Complex counting, joining and grouping

Complex counting, joining and grouping

Sql Comparing two tables

Blog Archive

About Me