Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Grouping pblm

Hi,

I have a query which returns several data fields and one of the grouping criteria is the time.
The data format for the time field resembles this: "10.11.2005 15:45:37" .. which is 'date.month.year calltime'.
I want the query to group data by the hour, therefore i wrote the query like this: Left([AllCalls.CALLTIME],13) AS HourlyCallTime, the HourlyCallTime field shows the data in this format: 10.11.2005 15, however, the grouping is not done. It only groups properly when I do this: Left([AllCalls.CALLTIME],12) AS HourlyCallTime, but then the problem is that the HourlyCallTime field does not show the proper format, it only displays this much: '10.11.2005 1'

I hope some1 can help me out :)

ThksI speak Oracle SQL; this is, as far as I can tell, not a language I know, but perhaps this piece of advice will help you ...

In Oracle, date format you wrote as an example (10.11.2005 15:45:37) is only one (of many possible) representations of a date column. Dates are stored as a number, and it is up to the developer to choose format he wants to present data to the end user.

Now, in Oracle, you should do this: FIRST format date column to desired format, and THEN write string functions on it.

For example, it would look like this:
- First part of the solution:
SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss') FROM ...

- Second part of the solution:
SELECT SUBSTR(TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss'), 1, 13) FROM ...

I really wouldn't know is this the case in your database, but - if nothing else shows up - you could try with this.|||Hello Littlefoot,

Thks 4 the quick rep, i've been working on it but now seem to be having sum other pblm,
my query

SELECT Zones.Zone, Left([AllCalls.CALLTIME],13) AS HourlyCallTime, SCCount.Connected, ((SCCount.Connected/(UCCount.NotConnected+SCCount.Connected)*100)) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone] And (AllCalls.PREFIX)=[SCCount].[PREFIX] And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone, Left([AllCalls.CALLTIME],13), SCCount.Connected, UCCount.NotConnected, AllCalls.PREFIX;

is not grouping all the calls, it's separating the connected and not connected such that am having twice the same row of data.
When I remove the SCCount.Connected and UCCount.NotConnected, it doesn't run, comes up with "query does not include the specified xpression .."

:S|||Hm, it seems that you, actually, do not want to GROUP data, but BREAK output on the hour. I'd say that use of a GROUP BY is meaningless if there's no aggregate function (such as MAX or AVG or COUNT) in the SELECT statement.

I don't know the tool you use (do you run this query on command prompt or in a reporting tool); if it is some kind of a report builder, you might want to use master-detail blocks of data.

On command prompt, all you can do is use of an ORDER BY clause and, eventually, use of (as Oracle provides) some kind of a BREAK command which will visually break data on the screen. Something like this:SQL> break on hire_year
SQL> select to_char(hiredate, 'yyyy') hire_year, ename, hiredate
2 from emp
3 order by 1, 2;

HIRE ENAME HIREDATE
-- ---- ---
1980 SMITH 17.12.80
1981 ALLEN 20.02.81
BLAKE 01.05.81
CLARK 09.06.81
FORD 03.12.81
JAMES 03.12.81
JONES 02.04.81
KING 17.11.81
MARTIN 28.09.81
TURNER 08.09.81
WARD 22.02.81
1982 MILLER 23.01.82

12 rows selected.

SQL>|||But I do want to group the data by zones and by the hour, but the query wouldn't run unless i include the connected n notconnected as part of the grouping criteria as well which is messing it up|||I assume Connected and NotConnectedand are some times and you calculete percentage. So if you group data by Zones etc. why don't you summarize times?

SELECT
Zones.Zone,
Left([AllCalls.CALLTIME],13) AS HourlyCallTime,
sum(SCCount.Connected),
((sum(SCCount.Connected)/(sum(UCCount.NotConnected+SCCount.Connected))*100) ) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone]
And (AllCalls.PREFIX)=[SCCount].[PREFIX]
And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone,
Left([AllCalls.CALLTIME],13),
AllCalls.PREFIX;

BTW you have too many brackets there. It's MS Access generated code, isn't it? :-) Why don't you select AllCalls.PREFIX if you group by it?|||Connected and notconnected are the count result from another query, and access wont let me run the query unless I have 'SCCount.Connected' and 'UCCount.NotConnected' in the grouping criteria ..

Yes, MS Access keeps adding loads of brackets when i run the query and go bak 2 sql view :Ssql

Grouping on multiple datasets - ANY suggestions

I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:

DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92

DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92

Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:

Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190

or

Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190

Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%

I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:

= count((Fields!sale_number.Value,"DS2"))

This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.

If I try something fancier such as:

=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))

The report won't even run.

I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).

I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.

Thank you,

cmk8895Joining datasets at a report level is not currently supported. It is on the wish list for a next release. Why don't you create the combo dataset at the data source level?

grouping data to a field

Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy

Grouping COUNT by two date fields in same table

Hi, I'm trying something which I'm sure should be quite simple but I am having a bit of trouble. Basically I have a call logging table which has a PK of CallID and then a Received Date column (RecvdDate) and Closed Date column (ClosedDate).

I need to return a single set of results showing how many calls were received and closed on a particular date. (N.B all records will have a RecvdDate, but not all will have a ClosedDate (i.e if the job has not been completed)).

Now, I can get the information with two seperate queries no problem:

Query 1

SELECT RecvdDate, COUNT(Callid)

FROM CallLog

GROUP BY RecvdDate

ORDER BY RecvdDate

Query 2

SELECT ClosedDate, COUNT(Callid)

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

ORDER BY ClosedDate

The problem is that I can't work out how to get the two counts to show together, grouped by each date, which I need for displaying on a single chart in SSRS. I'm thinking I might need a variable for the date to group by, but then i get lost

The ideal results set would look like this:

Date Total received Total Closed 28/02/2007 54 43 01/03/2007 22 21 02/03/2007 122 104 03/03/2007 33 41 04/03/2007 44 33 05/03/2007 76 56 06/03/2007 34 40 07/03/2007 87 80 08/03/2007 56 45 09/03/2007 42 31 10/03/2007 72 66

Can anybody help with this?

Thanks

Matt

try this one

select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as TotalRecvd
, count(ClosedDate) as TotalClosed
from CallLog
group by
convert(varchar(10),RecvdDate,101)|||disregard my prev post.. try this one instead

select [Date]
, SUM(CASE WHEN Rem = 'Recieved' THEN Calls ELSE 0 END) AS TotalReceived
, SUM(CASE WHEN Rem = 'Closed' THEN Calls ELSE 0 END) AS TotalClosed
FROM (
select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as Calls
, 'Recieved' as Rem
from CallLog
group by
convert(varchar(10),RecvdDate,101)
union all
select convert(varchar(10),ClosedDate,101) as [Date]
, count(ClosedDate) as Calls
, 'Closed' as Rem
from #temp
group by
convert(varchar(10),ClosedDate,101)
) CallLogs
GROUP BY
[Date]|||

Hi, thanks for the reply.

The problem with this solution (I had already tried something similar) is that it returns identical values for both Received and Closed calls, which I know is not the case. Here's the results I got:

04/01/2007 32 32 09/01/2007 62 62 10/01/2007 37 37 12/01/2007 45 45 16/01/2007 55 55 25/01/2007 77 77 02/02/2007 69 69 08/02/2007 106 106 11/02/2007 19 19 17/02/2007 13 13

For example, from manually searching table I know that for 17/02/2007 there were 16 Received calls and 13 Closed calls. However the SELECT statement we've tried doesn't include the three calls which were logged on 17/02/2007 but not closed.

I think the reason for this is because it is being GROUPED by RecvdDate, which doesn't make logical sense as it is possible that a call can be received one day and closed x number of days later.

Do you have any more ideas?

Thanks

Matt

|||

Okay thanks again, have seen your second post now and this solution has worked perfectly. I must admit I don't fully understand it, but it works!

Thanks a lot for your help.

Matt

|||

Matt:

Maybe a 1-pass select like this will work:

Code Snippet

declare @.mockup table
( CallID integer,
RecvdDate datetime,
ClosedDate datetime
)
insert into @.mockup
select 1, '1/1/7', null union all
select 2, '1/1/7', '1/1/7' union all
select 3, '1/5/7', '1/11/7' union all
select 4, '1/19/7', '1/11/7'

select case when dateType = 1 then RecvdDate
else ClosedDate
end as Date,
sum (dateType) as RecvdCount,
sum (case when dateType=0 then 1 else 0 end)
as ClosedCount
from @.mockup a
join (select 0 as dateType union all select 1) b
on dateType = 1 or ClosedDate is not null
group by case when dateType = 1 then RecvdDate
else ClosedDate
end

/*
Date RecvdCount ClosedCount
-- -- --
2007-01-01 00:00:00.000 2 1
2007-01-05 00:00:00.000 1 0
2007-01-11 00:00:00.000 0 2
2007-01-19 00:00:00.000 1 0
*/

|||

Try joining both results by the date.

Code Snippet

select

coalesce(a.d, b.d) as new_d,

isnull(a.cnt, 0) as received,

isnull(b.cnt, 0) as closed

from

(

SELECT RecvdDate as d, COUNT(Callid) as cnt

FROM CallLog

GROUP BY RecvdDate

) as a

full join

(

SELECT ClosedDate as d, COUNT(Callid) as cnt

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

) as b

on a.d = b.d

order by new_d;

AMB

|||Thanks to both hunchback and Kent: both of these solutions also work

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

Grouping and summing

HI,
I have a group on two fields (ie SalesPersonId and CustomerID). I want
to print a total after any of this values change (ie. If CustomerId
changes, a total line for the customerID will be printed but not for
the SalesPersonId). How Can I do it with running values ?
Best regards=runningvalue(sum, Fields!xxx.value, "CustomerID")
where "CustomerID" is the name for your customer group. I would make
sure to name your group something other than "table1_Group1" for
clarification purposes.
Fab wrote:
> HI,
> I have a group on two fields (ie SalesPersonId and CustomerID). I want
> to print a total after any of this values change (ie. If CustomerId
> changes, a total line for the customerID will be printed but not for
> the SalesPersonId). How Can I do it with running values ?
> Best regards

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:
>If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?
>|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
--
David Portas
SQL Server MVP
--|||Thanks Steve for the solution and the explanation, that's what I was looking for.
"Steve Kass" wrote:
> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
> >If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?
> >
> >
>

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals
query. Is there a way to do that in sql?
Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?
|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:

>If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a total
s query. Is there a way to do that in sql?
>
|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
David Portas
SQL Server MVP
|||Thanks Steve for the solution and the explanation, that's what I was looking for.
"Steve Kass" wrote:
[vbcol=seagreen]
> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
als query. Is there a way to do that in sql?
>

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a coup
le of fields in the last entry in the table for that id number. One field is
date so I can use max for that field, but how about the others. In Access y
ou can use Last in a totals
query. Is there a way to do that in sql?Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:

>If I use a group by sp to group by id number, is there any way to get a couple of f
ields in the last entry in the table for that id number. One field is date so I can
use max for that field, but how about the others. In Access you can use Last in a to
tal
s query. Is there a way to do that in sql?
>|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
David Portas
SQL Server MVP
--|||Thanks Steve for the solution and the explanation, that's what I was looking
for.
"Steve Kass" wrote:

> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
>
als query. Is there a way to do that in sql?[vbcol=seagreen]
>

Group by problem I think.....

Hi,
I have a table called Eyeballs.
Within it I have the following fields.
EyeballID
CustomerID
EyeballDate
Price
Width
Lenght
I need to extract a list of all customers latest eyeball.
I have tried various group by statements but cannot seem to nail it.
Any help much appreciated.Try,
select *
from Eyeballs as a
where EyeballDate = (select max(b.EyeballDate) from Eyeballs as b where
b.EyeballID = a.EyeballID)
go
AMB
"doc" wrote:

> Hi,
> I have a table called Eyeballs.
> Within it I have the following fields.
> EyeballID
> CustomerID
> EyeballDate
> Price
> Width
> Lenght
> I need to extract a list of all customers latest eyeball.
> I have tried various group by statements but cannot seem to nail it.
> Any help much appreciated.
>|||>I need to extract a list of all customers latest eyeball
hahhahaa
It ain't pretty, but I think you're looking for something like:
select
EyeballID
CustomerID
max(eyeballdate) as eye_date
Price
Width
Lenght
from eyeballs
group by customerid|||group by customerid,EyeballID,Price,Width,Lenght (if necessary to display
those colums)
Jens.
<roy.anderson@.gmail.com> schrieb im Newsbeitrag
news:1113410931.602799.211200@.o13g2000cwo.googlegroups.com...
> hahhahaa
> It ain't pretty, but I think you're looking for something like:
> select
> EyeballID
> CustomerID
> max(eyeballdate) as eye_date
> Price
> Width
> Lenght
> from eyeballs
> group by customerid
>|||What is the primary key? Please post a CREATE TABLE statement for the
table, INCLUDING keys and other constraints, otherwise we just have to
guess at what you want. It also helps to post a few INSERT statements
of sample data and show your required end result.
An untested guess, assuming (customerid, eyeballdate) is unique:
SELECT eyeballid, customerid, eyeballdate, price, width, length
FROM Eyeballs AS T
WHERE eyeballdate =
(SELECT MAX(eyeballdate)
FROM Eyeballs
WHERE customerid = T.customerid)
David Portas
SQL Server MVP
--|||> It ain't pretty
... and it won't work either I'm afraid.
David Portas
SQL Server MVP
--

Group by Max Count ? how to do query?

Can anyone tell me how to do this query please?
3 fields. provider, site, visitdate. Given a date range for
visitdate,
Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).
Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.
THANKS A LOTSome DDL (i.e. create table statements) would be useful.
Thomas
<bringmewater@.gmail.com> wrote in message
news:1114630228.267194.170960@.g14g2000cwa.googlegroups.com...
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>|||Try,
create view v1
as
select
provider,
site,
count(*) as number_of_visit
from
t1
where
visitdate >= convert(char(8), @.sd, 112) and visitdate < dateadd(day, 1,
convert(char(8), @.ed, 112))
group by
provider,
site
having
count(*) > 0
go
select
provider,
site,
number_of_visit
from
v1 as a
where
number_of_visit = (select max(b.number_of_visit) from v1 as b where
b.provider = a.provider)
go
AMB
"bringmewater@.gmail.com" wrote:

> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>

Group By Issue

I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

Select A, B, Count(*)
From Table
where ...
Group By B

It won't work on SQL unless you group both fields. Any way to get around this?

Thanks!

J827Think about it, and you'll see that it makes no logical sense.

If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

Select A, B, Count(*)
From Table
where ...
Group By A, B

If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

Select min(A) as A, B, Count(*)
From Table
where ...
Group By B

I think you need to better define, or at least better explain, what your objective is.|||blindman,

Thank you for your quick response!

The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

J827|||Calculate your B totals in a subquery:

select TableA.A, Subquery.B, Subquery.RecordCount
from TableA
left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
on TableA.B = Subquery.B|||GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.

GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes.

GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.

group by all except one

Hello,
i want to ask whether exist some possibility to make sql query grouping all fields except one field. Because is annoying when you must make select something like:

SELECT a,b,c,d,e,count(f) FROM x GROUP BY a,b,c,d,e

when easiest way is something like.

SELECT a,b,c,d,e,count(f) from x GROUP BY ALL except f.

Is something like 'except' operator possible? and in other databases?

Thank youNo.

Group By (part of a field)

In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
But for a unknown reason, maybe laziness, my users fill in all data in
Model.
How can i Group on the text before the first space i like to Group.
Examples:
Mondeo Gls
Galaxy 2.0
C220 2.0 D
C220 2.5 tdi
Scenic 2.0
Scenic 2.2
320 TDS
320 TD
So i have groups like Mondeo, Galaxy, C220, Scenic and 320
How can i do this ?
Thx for your help
GL.Well, I would suggest you clean up your data, and consider making domains
(likely in related tables) for each of those columns. Frankly I am amazed
that you dont have the following data:
Mondo Gls
Galaxy_2.0
C220 2.0 D
C22 2.5 tdi
Schenic 2.0
Scenic 2.2
3200 TDS
320 TD
So I am kind of thinking that this has to be more than just user entered
data. The answer to your problem on a temporary basis (since you have
indicated that they all have an initial space as a seperator is:
declare @.table table (value varchar(30))
insert into @.table
select 'Mondeo Gls'
union all
select 'Galaxy 2.0'
union all
select 'C220 2.0 D'
union all
select 'C220 2.5 tdi'
union all
select 'Scenic 2.0'
union all
select 'Scenic 2.2'
union all
select '320 TDS'
union all
select '320 TD'
select left(value,charindex(' ', value)) as model, count(*)
from @.table
group by left(value,charindex(' ', value))
model
-- --
320 2
C220 2
Galaxy 1
Mondeo 1
Scenic 2
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:GuZEf.233382$KY3.7519286@.phobos.telenet-ops.be...
> In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
> But for a unknown reason, maybe laziness, my users fill in all data in
> Model.
> How can i Group on the text before the first space i like to Group.
> Examples:
> Mondeo Gls
> Galaxy 2.0
> C220 2.0 D
> C220 2.5 tdi
> Scenic 2.0
> Scenic 2.2
> 320 TDS
> 320 TD
> So i have groups like Mondeo, Galaxy, C220, Scenic and 320
> How can i do this ?
> Thx for your help
> GL.
>|||DECLARE @.SomeCar VARCHAR(100)
SET @.SomeCar = 'Scenic 2.2'
SELECT
SUBSTRING(@.SomeCar, 1,CHARINDEX(CHAR(32),@.SomeCar)-1)
YOu can group by this expression.
HTH, Jens Suessmeyer.|||Thanx for your help. I have take over some parts and this is what works fine
for me.
SELECT left(carModel,charindex(' ',carModel)) as model
From tblVehicles
GROUP BY left(carModel,charindex(' ',carModel))")|||"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:w1_Ef.233447$UW5.7562339@.phobos.telenet-ops.be...

> Thanx for your help. I have take over some parts and this is what works
> fine for me.
> SELECT left(carModel,charindex(' ',carModel)) as model
> From tblVehicles
> GROUP BY left(carModel,charindex(' ',carModel))")
Yes, but you should SERIOUSLY consider cleaning up your data, and amending
the poor design of your GUI so that your users are forced to enter values in
the correct fields.

groug query

I m having 2 details table as purdtl and saledtl
i wanted to write a query by joining both details tables
purdtl table is having fields like itemcode,
batchno,qty,purno,rate,vatrate,vatamt
saledtl is having fields like itemcode, batchno,qty,saleno,irqty
i want to write a query having
purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from both the tables
plese help me for writing above query
--
Yousuf Khan
ProgrammerHi
SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
purdtl.itemcode=saledtl.itemcode
Take a look at GROUP BY clause in the BOL
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>I m having 2 details table as purdtl and saledtl
> i wanted to write a query by joining both details tables
> purdtl table is having fields like itemcode,
> batchno,qty,purno,rate,vatrate,vatamt
> saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> i want to write a query having
> purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> from both the tables
> plese help me for writing above query
>
>
>
> --
> Yousuf Khan
> Programmer|||Can you please give me example using my tables i am not able to group it coz
both table are detail tables
]
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> Hi
> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> purdtl.itemcode=saledtl.itemcode
> Take a look at GROUP BY clause in the BOL
>
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >I m having 2 details table as purdtl and saledtl
> > i wanted to write a query by joining both details tables
> >
> > purdtl table is having fields like itemcode,
> > batchno,qty,purno,rate,vatrate,vatamt
> >
> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >
> > i want to write a query having
> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> > from both the tables
> > plese help me for writing above query
> >
> >
> >
> >
> >
> >
> >
> > --
> > Yousuf Khan
> > Programmer
>
>|||> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
Yes , if you provide DDL+ sample data + expected result
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
> ]
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>|||i have already told in my first question how i wanted the result and
i have given the fields also
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> >
> > ]
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> purdtl.itemcode=saledtl.itemcode
> >>
> >> Take a look at GROUP BY clause in the BOL
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >I m having 2 details table as purdtl and saledtl
> >> > i wanted to write a query by joining both details tables
> >> >
> >> > purdtl table is having fields like itemcode,
> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >
> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >
> >> > i want to write a query having
> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> > from both the tables
> >> > plese help me for writing above query
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >>
> >>
> >>
>
>|||Its not enough info.
On what column you want to join? If you cant join tables directly, what is
the relationship? You also didnt post sample data, post a couple of rows in
both tables and then tell us what the result should be...
MC
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>i have already told in my first question how i wanted the result and
> i have given the fields also
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> Yes , if you provide DDL+ sample data + expected result
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> >
>> > ]
>> > --
>> > Yousuf Khan
>> > Programmer
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> >> purdtl.itemcode=saledtl.itemcode
>> >>
>> >> Take a look at GROUP BY clause in the BOL
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >> >I m having 2 details table as purdtl and saledtl
>> >> > i wanted to write a query by joining both details tables
>> >> >
>> >> > purdtl table is having fields like itemcode,
>> >> > batchno,qty,purno,rate,vatrate,vatamt
>> >> >
>> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >> >
>> >> > i want to write a query having
>> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> >> > from both the tables
>> >> > plese help me for writing above query
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Yousuf Khan
>> >> > Programmer
>> >>
>> >>
>> >>
>>|||Hi
look at this
Select purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from
purdtl,saledtl
where purdtl.itemcode = saledtl.itemcode
and purdtl.batchno = saledtl.batchno
group by
purdtl.itemcode,purdtl.batchno,saledtl.itemcode,saledtl.batchno|||And you try so hard:( :)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
>> ]
>> --
>> Yousuf Khan
>> Programmer
>>
>> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 and
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:
> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows in
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
> >i have already told in my first question how i wanted the result and
> > i have given the fields also
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >>
> >> Yes , if you provide DDL+ sample data + expected result
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >> >
> >> > ]
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Hi
> >> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> >> purdtl.itemcode=saledtl.itemcode
> >> >>
> >> >> Take a look at GROUP BY clause in the BOL
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >> >I m having 2 details table as purdtl and saledtl
> >> >> > i wanted to write a query by joining both details tables
> >> >> >
> >> >> > purdtl table is having fields like itemcode,
> >> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >> >
> >> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >> >
> >> >> > i want to write a query having
> >> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> >> > from both the tables
> >> >> > plese help me for writing above query
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Yousuf Khan
> >> >> > Programmer
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

groug query

I m having 2 details table as purdtl and saledtl
i wanted to write a query by joining both details tables
purdtl table is having fields like itemcode,
batchno,qty,purno,rate,vatrate,vatamt
saledtl is having fields like itemcode, batchno,qty,saleno,irqty
i want to write a query having
purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from both the tables
plese help me for writing above query
Yousuf Khan
ProgrammerHi
SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
purdtl.itemcode=saledtl.itemcode
Take a look at GROUP BY clause in the BOL
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>I m having 2 details table as purdtl and saledtl
> i wanted to write a query by joining both details tables
> purdtl table is having fields like itemcode,
> batchno,qty,purno,rate,vatrate,vatamt
> saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> i want to write a query having
> purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> from both the tables
> plese help me for writing above query
>
>
>
> --
> Yousuf Khan
> Programmer|||Can you please give me example using my tables i am not able to group it coz
both table are detail tables
]
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:

> Hi
> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> purdtl.itemcode=saledtl.itemcode
> Take a look at GROUP BY clause in the BOL
>
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>
>|||> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
Yes , if you provide DDL+ sample data + expected result
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...[vbcol=seagreen]
> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
> ]
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>|||i have already told in my first question how i wanted the result and
i have given the fields also
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:

> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>
>|||Its not enough info.
On what column you want to join? If you cant join tables directly, what is
the relationship? You also didnt post sample data, post a couple of rows in
both tables and then tell us what the result should be...
MC
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...[vbcol=seagreen]
>i have already told in my first question how i wanted the result and
> i have given the fields also
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>|||Hi
look at this
Select purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from
purdtl,saledtl
where purdtl.itemcode = saledtl.itemcode
and purdtl.batchno = saledtl.batchno
group by
purdtl.itemcode,purdtl.batchno,saledtl.itemcode,saledtl.batchno|||And you try so hard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 an
d
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:

> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows i
n
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>
>

GridView Help

Hi,

I use WVD and SQL Express 2005.

I have a table "SignIn" that one of fields inserted automatically by getdate()

And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in.

Currently I display all records at once.

My problem is how to make the GridView show today's records only.

I tried this code below, but I get only this message "There are no data records to display."

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:RC1%>"

ProviderName="<%$ ConnectionStrings:RC1.ProviderName%>"

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

<SelectParameters>

<asp:QueryStringParameterName="SignIn"QueryStringField="Format(Now, "M/dd/yyyy")" Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

Help Please!

I don't think you need use QueryStringParameter here. You can add the selectparameter from page load to assign today's date to it:

Like:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlDataSource1.SelectParameters.Add(

"SignIn",DateTime.Today.ToString("MM/dd/yyyy"));

}

}

The following is for a QueryStringParameter, but you don't need it.

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

<SelectParameters>

<%

-- <asp:QueryStringParameter DefaultValue="01/01/2007" Name="SignIn" QueryStringField="mydate" Type="DateTime" />

--

%><asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

Your URL will look something like this:

http://localhost:2013/WebSite1/queryDate.aspx?mydate=1/2/2006

|||

Thank you Limno,

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

This SelectCommand solved my problem.

I tried the

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

And I inserted SelectParameters like this:

<asp:SqlDataSourceID="SqlDataSource1" . . .

.

.

.

<SelectParameters>

<asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

I keep getting the following error:

The variable name '@.SignIn' has already been declared. Variable names must be unique within a query batch or stored procedure

I wonder what I did wrong?

Thank you.

|||

alexmu06:

Thank you Limno,

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

This SelectCommand solved my problem.

I tried the

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

And I inserted SelectParameters like this:

<asp:SqlDataSourceID="SqlDataSource1" . . .

.

.

.

<SelectParameters>

<asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

I keep getting the following error:

The variable name '@.SignIn' has already been declared. Variable names must be unique within a query batch or stored procedure

I wonder what I did wrong?

Thank you.

When you add the selectparameter from your code, you cannot use the declaratively again. You can use only one of them. I would prefer teh code one in your case and I hope this answers your question.

|||

Sorry but i don't get it.

if its possible could you expand a bit.

Thanks

|||

Hello:

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

and the following is all you need.

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

Please do not add that QueryStringParameter in your SelectParameters for this "SignIn"(you've already had it from the above code).

|||

Thank you,

I'll try that

Alex