creating data for a histogram.

[chudson007@hotmail.com]

I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarán

[Erland Sommarskog]

Re: creating data for a histogram.

(chudson007@hot mail.com) writes:[color=blue]
> I have a table, TableA with amongst other fields, a field for Qty.
> Qty can range from 0 to 100.
> How do I count the number of rows with a qty between 1 and 10, 11 and
> 20, 21 and 30, and so on using one SQL statement?[/color]

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[chudson007@hotmail.com]

Re: creating data for a histogram.

That seems to just count the number of times each qty appears, just
like

SELECT qty AS Expr1, COUNT(*) AS Expr2
FROM [Temp]
GROUP BY qty


How should I change it so that it counts the the number of qtys between
each range of 10?

Regards,
Ciarán

[jsfromynr]

Re: creating data for a histogram.

Hi Erland Sommarskog ,

You Always give helpfull and informative answers.
I changed the query a bit to show the LowRange as well HiRange
SELECT LowRange,HiRang e,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
but I am facing a problem can You guide me on this

This query {select q=qty+10 from sales order by q} works but
{select q=qty+10 from sales group by q} does not work .SQL Server2000
is not recognising Aliased Columns in second case .
--------
With regards
Jatinder Singh (System Analyst )

[Erland Sommarskog]

Re: creating data for a histogram.

(chudson007@hot mail.com) writes:[color=blue]
> That seems to just count the number of times each qty appears, just
> like
>
> SELECT qty AS Expr1, COUNT(*) AS Expr2
> FROM [Temp]
> GROUP BY qty
>
>
> How should I change it so that it counts the the number of qtys between
> each range of 10?[/color]

The query I posted was:

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

I would expect to give the desired result, assuming that qty is integer.
If qty is float or decimal, it will indeed just be a roundabout way to
count single qtys.

I will have to admit that I did not test my query, but there is standard
recommendation that posting asking for help with queries should include:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired output given the sample data.

This makes it very easy for me or anyone else who anser to cut and paste
into Query Analyzer and test whatever we post.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> You Always give helpfull and informative answers.
> I changed the query a bit to show the LowRange as well HiRange
> SELECT LowRange,HiRang e,COUNT(*)
> FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
> ,HiRange=((qty - 1) / 10) * 10 + 10
> FROM sales) AS ds
> GROUP BY lowRange ,HiRange
> but I am facing a problem can You guide me on this
>
> This query {select q=qty+10 from sales order by q} works but
> {select q=qty+10 from sales group by q} does not work .SQL Server2000
> is not recognising Aliased Columns in second case .[/color]

Correct. I believe that Access does this, but that's not in alignment with
the SQL standards.

Instead, the technique to use is a derived table as a above.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[jsfromynr]

Re: creating data for a histogram.

Thanks Erland,
I am facing another problem of displaying a summarised data along with
the detail data
i.e

Item Qty

Item1 10
Item1 10
Item1 20
40 ( Sum for Item1)
and so on ............

I wish to have a single query which runs on all RDBMS . Is it possible
?

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

This Query works but it would work on MS SQLServer .

-------------------------------------------------
With regards
Jatinder Singh (System Analyst )

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> I am facing another problem of displaying a summarised data along with
> the detail data
> i.e
>
> Item Qty
>
> Item1 10
> Item1 10
> Item1 20
> 40 ( Sum for Item1)
> and so on ............
>
> I wish to have a single query which runs on all RDBMS . Is it possible
> ?
>
> USE pubs
> SELECT type, price, advance
> FROM titles
> ORDER BY type
> COMPUTE SUM(price), SUM(advance) BY type
>
> This Query works but it would work on MS SQLServer .[/color]

Here is a query which I believe should be fairly portable. (But since
I only work with SQL Server, I can make no warranties):

SELECT type, x = '', price, advance
FROM titles
UNION
SELECT type, 'Total', SUM(price), SUM(advance)
FROM titles
GROUP BY type
ORDER BY type, x

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[jsfromynr]

Re: creating data for a histogram.



Erland Sommarskog wrote:[color=blue]
> jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=green]
> > I am facing another problem of displaying a summarised data along[/color][/color]
with[color=blue][color=green]
> > the detail data
> > i.e
> >
> > Item Qty
> >
> > Item1 10
> > Item1 10
> > Item1 20
> > 40 ( Sum for Item1)
> > and so on ............
> >
> > I wish to have a single query which runs on all RDBMS . Is it[/color][/color]
possible[color=blue][color=green]
> > ?
> >
> > USE pubs
> > SELECT type, price, advance
> > FROM titles
> > ORDER BY type
> > COMPUTE SUM(price), SUM(advance) BY type
> >
> > This Query works but it would work on MS SQLServer .[/color]
>
> Here is a query which I believe should be fairly portable. (But since
> I only work with SQL Server, I can make no warranties):
>
> SELECT type, x = '', price, advance
> FROM titles
> UNION
> SELECT type, 'Total', SUM(price), SUM(advance)
> FROM titles
> GROUP BY type
> ORDER BY type, x
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

Hi Erland,
Thanks ,I think it will work on any RDBMS . Your analysis ablity is
really something. I cannot describe it in words.

Thanks Again
With warm regards
Jatinder Singh (System Analyst)

[jsfromynr]

Re: creating data for a histogram.

Hi Erland,

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

With warm regards
Jatinder Singh (System Analyst)

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> Can we similarly replace CUBE operator in SQL Server by using simple
> queries that will run on any RDBMS?[/color]

I don't use CUBE very often, so I may miss some fine detail. But the
two queries below returns the same result:

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id WITH CUBE
ORDER BY type, pub_id

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id
UNION
SELECT type, NULL, SUM(price), SUM(advance)
FROM titles
GROUP BY type
UNION
SELECT NULL, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY pub_id
UNION
SELECT NULL, NULL, SUM(price), SUM(advance)
FROM titles
ORDER BY type, pub_id

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[jsfromynr]

Re: creating data for a histogram.

Hi Erland
I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds


In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds


Query 1:-
select RowId,PolicyNum ber,EndoNumber, status,SF,case when SubQ is Null
then 'No' else 'Yes' end as Lock,RCount from
(
SELECT RowId,PolicyNum ber,EndoNumber, status,SF,
(select status from InProcessData05 2005MstM WHERE status in ('pen') and
IP.PolicyNumber =PolicyNumber and IP.EndoNumber=E ndoNumber) as subQ,
(select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and IP.PolicyNumber =PolicyNumber and
IP.EndoNumber=E ndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData05 2005MstM IP
WHERE status in ('cur','pen')
) X
ORDER BY PolicyNumber

select getdate()


SELECT
RowId,PolicyNum ber,EndoNumber, status,SF,dbo.f nTryGetPolicyCo unt(PolicyNumbe r,EndoNumber)
as RCount,
dbo.fnTryGetPol icyLock(PolicyN umber,EndoNumbe r) as Lock
--(select status from InProcessData05 2005MstM WHERE status in ('pen')
and IP.PolicyNumber =PolicyNumber and IP.EndoNumber=E ndoNumber) as subQ,
--(select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and IP.PolicyNumber =PolicyNumber and
IP.EndoNumber=E ndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData05 2005MstM
WHERE status in ('cur','pen')
order by PolicyNumber
select getdate()


create function fnTryGetPolicyC ount(@p varchar(16),@e varchar(3))
returns int
as
begin
return (select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and PolicyNumber=@p and EndoNumber=@e)
end



create function fnTryGetPolicyL ock(@p varchar(16),@e varchar(3))
returns varchar(3)
as
begin
declare @Lock varchar(3)
select @Lock=status from InProcessData05 2005MstM WHERE status in
('pen') and PolicyNumber=@p and EndoNumber=@E
if @Lock is null
set @Lock='No'
else
set @Lock='Yes'

return (@Lock)
--(select status from InProcessData05 2005MstM WHERE status in ('pen')
and PolicyNumber=@p and EndoNumber=@E)
end

----------------------------------------------------------
Jatinder

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> I am bit confused by the output produced by the Query Analyzer and
> finding it bit difficult to decide which one of the following query is
> faster.
>
> In query one I am using Correlated subquery Approach and it consumes
> 78% of batch time when run with 2nd query but time of mere 20
> micrseconds
>
>
> In query two I am using functions (these functions berely takes
> PolicyNumber and Endrosment No to give output and does the same query )
> Approach and it consumes 22% of batch time when run with 1st query but
> time of 400 micrseconds[/color]

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @d datetime, @tookms int
SELECT @d = getdate()
-- run query here
SELECT @tookms = datediff(ms, @d, getdate())
PRINT 'This query took ' + ltrim(str(@took ms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFER S to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microsecon ds". I assume this is a typo for
"millisecon ds".

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[jsfromynr]

Re: creating data for a histogram.

Hi Erland,

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.


Waiting for your reply.


Jatinder

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> Thanks for your answer and time.
> Sorry for the typo error.
> I can be wrong in my assumption buut isn't it that the two queries are
> working in simliar fashion. Both are taking a value(or two) passing it
> to inner corelated query (funtion) and getting the result.[/color]

Just because two queries logically are the same, that does not mean that
performance is. There is quite some overhead with calls to saclar user-
defined functions. Also, when you stuff a subquery into a scalar function,
all the optimizer sees is a call, it does not see the contents of rhe
UDF, so it cannot take any shortcuts.

Table-valued functions are different. Particularly inline functions. Table-
valued inline functions are really just macros, and the query text is
pasted into the query, so the optimizer can rearrange as it likes.

As for the estimates you saw in Query Analyzer, they are just estimates, and
I would not pay too much attention on them.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp