Grouping

Hi,

I have a stored procedure which is returning results in the following format:

No.of substances Count

1 29

2 89

3 876

.. ..

15 56

16 89

Now i need to display like this:

No.of substances Count %

1 20

2 89

.. ..

>=9 8766

Total

All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9

How to achieve this

Thanks in advance

Hi,

I done the grouping at stored procedure level.

Thanks

Group by query returning too many rows

Can anyone help ?
Why is this query:

select bp, sum(msg) as 'msg'
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

returning 666 rows, while this query

select bp
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

is only returning 20.
The "correct" result is 20 rows, one for each bp.
The fisrt query returns alot of duplicate bp.

By the way: What is faster: Converting the sqldate field to a varchar and comparing with another varchar, or converting the varchar to a date and then comparing it to the sqldate field ?do you want the SUM of all the msg attributes or the number of msg (messages?) for each bp? try using COUNT(*) in place of the SUM(msg).

In your case you are probably doing table scans due to the convertion of the date attribute to a varchar. SQL Server can efficiently convert and test a (var)char variable to a date attribute + you can take advantage of indexes.|||Sorry, I guess I should have made things clearer.
What I want is the sum of all the numbers stored in the msg column, i.e. the number of msgs for each bp. So the result set should have one row for each bp. This works fine without the sum(msg) part, and the result looks something like this:

HENNES
HENTEXTRA
KANAL5

But when I add the sum(msg) to get the number of messages pr. bp then the result looks like this:

Wow. Something strange just happened. When I ran the query to produce the results I added "order by bp" at the end, and then there was suddenly just one row for each 20 in total. Without it the resultset returns 666 rows.

Is the group by clause dependent upon the order one retrieves the rows ?|||No. The order by is used to sort the result set and does not affect the group by.

I set up a simple test...

Code:
----------------------------
create table #tmp(f1 varchar(10),f2 int)
insert into #tmp values('A',2)
insert into #tmp values('A',4)
insert into #tmp values('C',3)
insert into #tmp values('C',1)
insert into #tmp values('C',1)
insert into #tmp values('B',2)
insert into #tmp values('B',3)
insert into #tmp values('B',4)

select f1,sum(f2) as 'Sum'
from #tmp
group by f1
order by f1
----------------------------

is this anything close to what you are working on?|||That is pretty much what I am working on, except that my view has alot more columns. At the moment I am really only interested in getting one row for each bp, with one sum of messages for each.
My query does produce the desired results, as long as I have the "order by bp" clause at the end.
So my problem is really solved, but I don't really understand why though. If you want to find out why, and need any more information from me just let me know.
The view I am querying is based on two other views, but I can't see that making much of a difference.

This is the result I was looking for, and I get with the order bp:

davinci 1333
E-CLIPS 1864
HENNES 1397
KANAL5 6470
MRJET 6
PASSAGEN 70
SIMONTV 12
SPORTAL 828
STARLIFE 1004
TISCALI 2484
YAHOO 3
...
...
20 rows in total

This is some of what I get without the order bp:
SPORTAL 8
davinci 11
E-CLIPS 11
davinci 1
E-CLIPS 1
davinci 7
E-CLIPS 7
davinci 9
E-CLIPS 9
davinci 2
E-CLIPS 2
davinci 8
...
...
...
666 rows in total

group by function not returning expected

Hi,
I am having trouble get the numbers that I need. I have a table that records
positions and the action that happened at that positon and the operator that
caused the action. I need to total the amounts per Operator per Action Code.
I tried to use GROUP BY but it only gave me one operator for each LotID. Thi
s
was the statement I used:
SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
1], ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
The following is an example of the data that is stored in the table.
LotID Operator Position Action Code
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
A1073 Susan 2946519574 11
826O3 Priscilla 960248867 11
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Angela 924485652 11
82603 Gloria 896646927 10
826O3 Priscilla 960248867 11
A1073 Susan 2946519574 11
82603 Angela 927628980 10
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Gloria 896646927 10
A1073 Carolyn 2915880805 10
82603 Angela 924485652 11Can you clarify this? The columns in your data do not match the columns in
your query. Also, do you need to sum (add stuff up) our count the rows? Also
,
you say you need the amounts per operator per action code but your group by
includes lot id.
"A.B." wrote:

> Hi,
> I am having trouble get the numbers that I need. I have a table that recor
ds
> positions and the action that happened at that positon and the operator th
at
> caused the action. I need to total the amounts per Operator per Action Cod
e.
> I tried to use GROUP BY but it only gave me one operator for each LotID. T
his
> was the statement I used:
> SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
> 1], ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> The following is an example of the data that is stored in the table.
> LotID Operator Position Action Co
de
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> A1073 Susan 2946519574 11
> 826O3 Priscilla 960248867 11
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Angela 924485652 11
> 82603 Gloria 896646927 10
> 826O3 Priscilla 960248867 11
> A1073 Susan 2946519574 11
> 82603 Angela 927628980 10
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Gloria 896646927 10
> A1073 Carolyn 2915880805 10
> 82603 Angela 924485652 11
>|||SELECT DISTINCT LotID, UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End
The LotID in order to connect the results to another query I have that gives
me the Lots that were run last w. Also I need the Sum of the rows.
"Kathi Kellenberger" wrote:
> Can you clarify this? The columns in your data do not match the columns in
> your query. Also, do you need to sum (add stuff up) our count the rows? Al
so,
> you say you need the amounts per operator per action code but your group b
y
> includes lot id.
>
>
> "A.B." wrote:
>|||With your query, you should get a row for every possible combination of
lotID, operator and action code. I'm not sure if that's what you are after.
"A.B." wrote:
> SELECT DISTINCT LotID, UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End
> The LotID in order to connect the results to another query I have that giv
es
> me the Lots that were run last w. Also I need the Sum of the rows.
> "Kathi Kellenberger" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. And your narrative is useless.|||That is what i want but this is an example of the results that I am getting:
62324 Pamela 9969832955 10
62324 Pamela 19966076115 11
62332 Susan 9641299760 11
62332 Teresa 9633011910 11
62334 Carolyn 9978382505 10
62334 Carolyn 19983575455 11
62334 Melissa 9719717360 10
I am only getting a one result for certain lots and several for others.
"Kathi Kellenberger" wrote:
> With your query, you should get a row for every possible combination of
> lotID, operator and action code. I'm not sure if that's what you are afte
r.
>
>
> "A.B." wrote:
>|||A.B.,
Kathy says you'll get one row for each combination of lotID, operator,
and action code,
and you say "That is what i want". This is exactly what you are
getting. The combinations
in your results are
(62324, Pamela, 10)
(62324, Pamela, 11)
(62332, Susan, 11)
(62332, Teresa, 11)
(62334, Carolyn, 10)
(62334, Carolyn, 11)
(62334, Melissa, 10)
You have more than one name and/or action code for some lotID values,
so you will get more than one row for those values. For example, for lotID
62334, you have information for Melissa with action code 10, and you have
information for Carolyn with action codes both 10 and 11. If you want only
one row for this lotID, do you want it to say Melissa or Carolyn, and do you
want the action code to be 10 or 11? You need to be more specific about
what your result is supposed to be.
Steve Kass
Drew University
A.B. wrote:
>That is what i want but this is an example of the results that I am getting
:
> 62324 Pamela 9969832955 10
> 62324 Pamela 19966076115 11
> 62332 Susan 9641299760 11
> 62332 Teresa 9633011910 11
> 62334 Carolyn 9978382505 10
> 62334 Carolyn 19983575455 11
> 62334 Melissa 9719717360 10
>I am only getting a one result for certain lots and several for others.
>"Kathi Kellenberger" wrote:
>
>|||No, because I am only getting the operator Pamela for Lot 62324 when actuall
y
there is four or five operators.
"Steve Kass" wrote:

> A.B.,
> Kathy says you'll get one row for each combination of lotID, operator,
> and action code,
> and you say "That is what i want". This is exactly what you are
> getting. The combinations
> in your results are
> (62324, Pamela, 10)
> (62324, Pamela, 11)
> (62332, Susan, 11)
> (62332, Teresa, 11)
> (62334, Carolyn, 10)
> (62334, Carolyn, 11)
> (62334, Melissa, 10)
> You have more than one name and/or action code for some lotID values,
> so you will get more than one row for those values. For example, for lotI
D
> 62334, you have information for Melissa with action code 10, and you have
> information for Carolyn with action codes both 10 and 11. If you want onl
y
> one row for this lotID, do you want it to say Melissa or Carolyn, and do y
ou
> want the action code to be 10 or 11? You need to be more specific about
> what your result is supposed to be.
> Steve Kass
> Drew University
>
> A.B. wrote:
>
>|||Ah. When you said "only one" for some and "several" for others, I
thought the problem was the "several", not the "one". ;)
My guess is that you are not showing us the entire query, since if there
is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
the result of
SELECT DISTINCT
LotID,
UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End 1],
ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
will definitely include a row showing 62324 with another operator.
Perhaps you
are noting the omission only after this query is used in a larger one, maybe
with an inner join that should be a left join - I can't be sure.
If you are certain that this is your query and that results are missing,
please show us both the results of this query and the result of
SELECT TOP 10
LotID,
UserName, 'Operator',
EncoderpositionDetectionEnd1,
ActionCode
FROM dbo.OperatorData
WHERE LotID = '62324'
AND UserName <> 'Pamela'
-- optionally add ORDER BY something...
SK
A.B. wrote:
>No, because I am only getting the operator Pamela for Lot 62324 when actual
ly
>there is four or five operators.
>"Steve Kass" wrote:
>
>|||I had a date in the where clause to make my results alot smaller and by
taking the date out of the where clause it allowed me to see all of the
operators. I am not sure why this happened but it is working now. Thanks for
your help man.
"Steve Kass" wrote:

> Ah. When you said "only one" for some and "several" for others, I
> thought the problem was the "several", not the "one". ;)
> My guess is that you are not showing us the entire query, since if there
> is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
> the result of
> SELECT DISTINCT
> LotID,
> UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End 1],
> ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> will definitely include a row showing 62324 with another operator.
> Perhaps you
> are noting the omission only after this query is used in a larger one, may
be
> with an inner join that should be a left join - I can't be sure.
> If you are certain that this is your query and that results are missing,
> please show us both the results of this query and the result of
> SELECT TOP 10
> LotID,
> UserName, 'Operator',
> EncoderpositionDetectionEnd1,
> ActionCode
> FROM dbo.OperatorData
> WHERE LotID = '62324'
> AND UserName <> 'Pamela'
> -- optionally add ORDER BY something...
> SK
> A.B. wrote:
>
>

Group By

Hi,

I have the report,Report datasource is stored procedure.The stored procedure is returning results like this:

ID Name Count

1 Own Residence 50

2 Other Residence 20

3 xxxx 89

4 vvvvvv 78

Now i want to display the report like in the following format:

Name Count

Residence

Own 50

Other 20

xxxx 89

vvvvvv 78

How to achevie this

Thanks in advance

Assuming you can't change your store procedure.

To do this you need two distinct tables on your report designer surface. One will have a filter to get rid of all names without 'Residence'. Then, you simply list the names and counts in the details section.

Right underneth, you need to add a second table without heading/footer. This one will have a filter to get rid of the name with 'Residence'. Make the column widths match the one of your first table and things should align properly as if you were using a single table.

Good luck.

Group By

Hi

I am having problems returning the necessary data with my SQL query. The query below is returning me the correct data but aswell as getting the datemodified column, I need to get the value in the ID column that is associated with the max date modified.

SELECT DISTINCT max([item].[datemodified])
FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]
WHERE [ItemDetails].[ItemTypeID] = 1
GROUP BY [ItemDetails].[ItemValue]

The items table also has an id column, basically I need to get the IDs of the most recent itemvalues. There are multipe entries row in the itemdetail table for the same itemvalue so that why I need to do the group by. In essence I need to order the individual groups, if this is possible.

Any help would be appreciated.

Here it is...

Code Snippet

--On SQL Server 2000

Select

Item.Id

, Item.[datemodified]

From

Item Join

(

SELECT DISTINCT max([item].[datemodified]) max_datemodified

FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

On Item.[datemodified] = Data.[max_datemodified]

--On SqlServer 2005

Select Id, datemodified

From

(

Select [Item].Id, [item].[datemodified], max([item].[datemodified]) Over (Partition BY 0) Max_datemodified

From [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

Where

datemodified = Max_datemodified

|||

Thanks for that, just had to change the Partition By 0 to Partition By [ItemDetails].[ItemValue] and it worked perfect.

Cheers.