Grouping columns

Hi,

I was trying to retrieve some data in such a way that it 2 columns will
be merged into one, with a column in between. I am trying to do
something like this:

SELECT LastName + ", " + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName

But SQL Server does not like this syntax (though it does work with
"LastName + FirstName").

I appreciate any help.

Thanks,
AaronSQL Server uses single quotes for strings, not double quotes. Also...
you probably want to order by the first name if the last name is the
same, correct? Try:

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName, FirstName

If it is possible for there to be NULL values or empty strings in
either of the columns then you will need to account for that as well.

HTH,
-Tom.|||SELECT LastName + ", " + FirstName AS Name
FROM EmployeeTBL
ORDER BY Name

This should work.|||Use single qutes instead of double:

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName|||Hmm, I didn't notice the double quotes ealier.

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY Name

You can always use the final column name in the ORDER BY condition.

Grouping by unrelated field- SQL masters, try this!

I would like to retrieve 10(dynamic) records of table x (proucts) for
each user in table y (users). Can this be done?
I would like the end result to be something like this: (would this be
a union?)
__________________________
y.name | x.pid | x.pname
Bob | 1 | fork
Bob | 2 | spoon
... | |
Bob | 10 | potato
Jeff | 11 | pen
etc....
__________________________
But also with the number to return based off of a query, ex-
select @.pcount = count(products)
select @.ucount = count(users)
select @.pcount / @.ucount
10
And lump all this in an Stored procedure
ex-
get number of total records in x, divide by total y = z
select z records for each user in y.
You would be a master in my book if you can give me hints on this one!
Thanks,
JeffHi

It is always better to post DDL ( CREATE TABLE statements etc...) and
example data (as insert statements) with the expected results that you
require from that data. That removes most of the ambiguities and reduces
that number of assumptions that someone answers your question will have to
make.

This seems to be something similar to what you require
http://tinyurl.com/28dhn

John

"JC" <ujjc001@.charter.net> wrote in message
news:b8c0d25d.0407061959.2f9791ca@.posting.google.c om...
> I would like to retrieve 10(dynamic) records of table x (proucts) for
> each user in table y (users). Can this be done?
> I would like the end result to be something like this: (would this be
> a union?)
> __________________________
> y.name | x.pid | x.pname
> Bob | 1 | fork
> Bob | 2 | spoon
> ... | |
> Bob | 10 | potato
> Jeff | 11 | pen
> etc....
> __________________________
> But also with the number to return based off of a query, ex-
> select @.pcount = count(products)
> select @.ucount = count(users)
> select @.pcount / @.ucount
> 10
> And lump all this in an Stored procedure
> ex-
> get number of total records in x, divide by total y = z
> select z records for each user in y.
> You would be a master in my book if you can give me hints on this one!
> Thanks,
> Jeffsql

group several rows in a SQL query

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

id value
1 1
2 3
3 5
4 7
5 9

The result I expect from the query are 3 rows: [9, 15,21]:
1st row: 1+3+5 = 9
2nd row: 3+5+7 = 15
3rd row: 5+7+9 = 21

Help is appreciated,
Guidoa few sql commands? how about just one? :)select this.value + prev.value + next.value as result
from daTable as this
inner
join daTable as prev
on prev.id =
( select max(id)
from daTable
where id < this.id )
inner
join daTable as next
on next.id =
( select min(id)
from daTable
where id > this.id )|||Thanks for your response, its awesome, but I dont think it fits my needs. I gave an example with just three consecutive rows accumulated, in real life it will be hundreds of rows.
The number of consecutive rows isnt fixed, either, it depends on user input.

Thank you anyway,
Guido|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

i'm done in this thread|||Asking the question you want anwsered? Hmm....a radical concept.

This code will work provided your IDs are consecutively numbered with no gaps:

declare @.RangeLength
set @.RangeLength = 3

select t1.id,
sum(t2.value)
from [YourTable] t1
inner join [YourTable] t2 on t2.id between t1.id-@.RangeLength+1 and t1.id
group by t1.id

If your IDs are not uniformly sequential, then things get more complex, and might best be solved using a temporary table.|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

I did... not as clearly as I should have, but I did:

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

@.blindman:
Yes, my IDs are consecutively numbered, without gaps. Ill try your solution, many thanks.

Guido|||Maybe this does what you want:WITH t(value, nr) AS
(SELECT value, (ROW_NUMBER() OVER (ORDER BY id) - 1)/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr(Just replace 17 by the number of rows you want to group.)|||Alternatively, if your SQL doesn't have ROW_NUMBER(), and/or if your "id" column just contains all numbers 1, 2, 3 etc. (which means they already contain the ROW_NUMBER()s), the following gives the same result:WITH t(value, nr) AS
(SELECT value, id/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nrOr possibly even, if your SQL allows grouping by expression:SELECT SUM(value)
FROM myTable
GROUP BY id/17or possibly (in case your SQL doesn't perform integer division):SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)|||Thanks Peter,

I will test your script, too.|||Guido

The following seems to work fine and may be easier to understand.
Again the id's need to be sequential but I believe you said they were.

Mike

select t1.val + t2.val + t3.val as vals
from my_table t1,
my_table t2,
my_table t3
where t1.id < t2.id
and t2.id < t3.id
and t3.id < t1.id + 3;|||Easier to understand than a single join? I don't think so.
Plus, you need to read GNiewerth's second post. Your solution is not scalable.|||you need to read GNiewerth's second post. Your solution is not scalable.
You're right - I didn't see that requirement - please ignore my code then!
Mike

Group By Date

I have an Employees Table, I want to retrieve the data using group by Date.
Select count(*),Joined_Date from Employees Group By Joined_Date.
The problem here is it groups by both date and time. I want to group by only
date.
I want the results for each and every day. Is there someother to do this
other than using groups.
Thanks
KiranHi,
Try something like this. It gives by dates and excludes the time
Select count(*),convert(varchar,Joined_Date,103
)
from Employees Group By convert(varchar,Joined_Date,103)
HTH..
Regards,
Dilip|||Kiran
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
GROUP BY CONVERT(CHAR(10),col2,112)
Note: Take a look at CONVERT system function that has a style (third)
parameter.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>I have an Employees Table, I want to retrieve the data using group by Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by
> only
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||Thanks Uri
"Uri Dimant" wrote:

> Kiran
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 DATETIME NOT NULL
> )
> INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
> INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
> INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
> INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
> INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
> INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
> INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
> INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
>
> SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
> GROUP BY CONVERT(CHAR(10),col2,112)
> Note: Take a look at CONVERT system function that has a style (third)
> parameter.
>
> "Kiran" <Kiran@.discussions.microsoft.com> wrote in message
> news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>
>|||Thanks Dilip
"dilipn123@.gmail.com" wrote:

> Hi,
> Try something like this. It gives by dates and excludes the time
> Select count(*),convert(varchar,Joined_Date,103
)
> from Employees Group By convert(varchar,Joined_Date,103)
> HTH..
> Regards,
> Dilip
>|||You have to convert the date to a non-time consisting datetime, like
Select CONVERT(VARCHAR(10),GETDATE(),112)
Select count(*),CONVERT(VARCHAR(10),Joined_Date
,112) from Employees Group By
CONVERT(VARCHAR(10),Joined_Date,112).
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kiran" wrote:

> I have an Employees Table, I want to retrieve the data using group by Date
.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by on
ly
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||In news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com,
Kiran <Kiran@.discussions.microsoft.com> said:
> I have an Employees Table, I want to retrieve the data using group by
> Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group
> by only date.
Why does everyone convert the date to char?
Select count(*),Joined_Date
from Employees
Group By cast(cast(Joined_Date as float) as int)
Casting the datetime to an int doesn't truncate, it rounds, which is IMHO
very odd indeed.
Steve|||> Why does everyone convert the date to char?
Converting dates to a number works well but defies common-sense logic.
I don't know why MS allows this weird conversion between dates and
numerics. Note that they only guarantee the consistency of round-trip
conversions between versions.
Another method:
...
GROUP BY DATEDIFF(DAY,0,joined_date)
David Portas
SQL Server MVP
--|||In news:1122975714.884084.99980@.g44g2000cwa.googlegroups.com,
David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> said:
> Converting dates to a number works well but defies common-sense logic.
> I don't know why MS allows this weird conversion between dates and
> numerics.
It isn't weird when you consider how datetime values are stored. They are
simply a floating point number where the integer part is the number of days
since 1/1/1900 and the fractional part is the fraction through the day (so
.5 is midday). Many programming languages follow this convention, although
the date base is often different and shouldn't be relied upon. It allows
simple calculation of time passage (for example, how many days between x and
y? Answer = y-x).
Steve|||> It isn't weird when you consider how datetime values are stored
Yes of course, but in SQL the datatypes are supposed to insulate us
from the way data is stored. MS might well choose to change the storage
architecture in a future version but that shouldn't change the logical
meaning of our code. That's why I'm uncomfortable with date/numeric
conversions, although I admit that I too find them convenient
sometimes.

> It allows
> simple calculation of time passage (for example, how many days between x a
nd
> y? Answer = y-x).
I would always recommend you use the proper date arithmetic functions
for this rather than rely on the numeric math operators. There's a
world of difference between y-x and DATEDIFF(DAY,x,y). For example:
DECLARE @.dt1 SMALLDATETIME, @.dt2 SMALLDATETIME
SET @.dt1 = '20050101'
SET @.dt2 = '19000101'
SELECT DATEDIFF(DAY,@.dt1,@.dt2)
SELECT @.dt2 - @.dt1
David Portas
SQL Server MVP
--

Group By

I want to retrieve the model of cars in Groups. However the field Model is
filled with the model and the type. Is there a way to group on the first
word, lets say 147, 156, ..
Thx GL
147 1.9 D
147 2.1 D
156 1.6
156 1.7
156 1.9 D
156 2.1 D
156 2.1 D
156 2.5 DHi Gerard,
Try this:
Create Table Model
(
Model varchar(20),
Price Money
)
Insert Into Model (Model, Price)
Values('147 1.9 D',15000)
Insert Into Model (Model, Price)
Values('147 2.1 D',16000)
Insert Into Model (Model, Price)
Values('156 1.6',17000)
Insert Into Model (Model, Price)
Values('156 1.7',18000)
Insert Into Model (Model, Price)
Values('156 1.9 D',20000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.5 D',27000)
Select Left(Model, 3) as 'Model', Avg(Price) as 'AvgPrice'

>From Model
Group By Left(Model, 3)
Drop Table Model
HTH
Barry|||If I'm understanding you...something like this works..
create table #table
(
car_string varchar(1000)
)
insert #table
select '147 1.9 D'
union all
select '147 2.1 D'
union all
select '156 1.6'
union all
select '156 1.7'
select substring(car_string,1,3) car_model,count(*)
from #table
group by substring(car_string,1,3)
order by 1 asc
HTH
MJKulangara
http://sqladventures.blogspot.com|||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.
Why do you have two data elemetns in one column? What the grep()
expression for this violation of 1NF? I woudl guess from this lack of
specs that you want to create a VIEW with the first three characters in
their own column, so you can do a GROUP BY on it.|||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.
Of course i can use left(model,3) if every model starts with a 3 charater
group.
So my exemple was wrong. It is not always the first 3 charaters. It is the
part before the first space i like to Group.
Like
Mondeo Gtd
Galaxy 2.0
C220 2.0 D
C220 2.5 tdi
Scenic 2.0
Scenic 2.2
320 TDS
320 TD
So i want the groups Mondeo, Galaxy, C220, Scenic and 320
How can i do this
GL.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> schreef in bericht
news:jlNEf.232245$XZ3.7544959@.phobos.telenet-ops.be...
>I want to retrieve the model of cars in Groups. However the field Model is
>filled with the model and the type. Is there a way to group on the first
>word, lets say 147, 156, ..
> Thx GL
> 147 1.9 D
> 147 2.1 D
> 156 1.6
> 156 1.7
> 156 1.9 D
> 156 2.1 D
> 156 2.1 D
> 156 2.5 D
>
>|||In that case - try this...
Create Table Model
(
Model varchar(20),
Price Money
)
Insert Into Model (Model, Price)
Values('147 1.9 D',15000)
Insert Into Model (Model, Price)
Values('147 2.1 D',16000)
Insert Into Model (Model, Price)
Values('156 1.6',17000)
Insert Into Model (Model, Price)
Values('156 1.7',18000)
Insert Into Model (Model, Price)
Values('156 1.9 D',20000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.5 D',27000)
Insert Into Model (Model, Price)
Values('Scenic 2.1 D',25000)
Insert Into Model (Model, Price)
Values('Scenic 2.5 D',27000)
Select Left(Model, Charindex(Space(1), Model)) as 'Model', Avg(Price)
as 'AvgPrice'

>From Model
Group By Left(Model, Charindex(Space(1), Model))
Drop Table Model|||IF model and type are supposed to be a limited set of values, meaning there
are valid values which are correct and anythign else is wrong, then you
would want to create a constraint on each of those fields to make sure the
values are valid. This could be done with a foreign key referencing a Model
table and a Type table. You could also make both of these fields required
(not null) and force the users to fill them in.
This will invariably create a stir with your users, but you should be able
to make the argument that having valid (and dependable) data validates the
need for the users changing how they do data entry.
Allowing data entry such as this to persist will only cause more problems
later on, particularly if some of the users are entering the data correctly,
and others are entering it incorrectly.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:qwYEf.233290$JY3.7518351@.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.
> Of course i can use left(model,3) if every model starts with a 3 charater
> group.
> So my exemple was wrong. It is not always the first 3 charaters. It is the
> part before the first space i like to Group.
> Like
> Mondeo Gtd
> Galaxy 2.0
> C220 2.0 D
> C220 2.5 tdi
> Scenic 2.0
> Scenic 2.2
> 320 TDS
> 320 TD
> So i want the groups Mondeo, Galaxy, C220, Scenic and 320
> How can i do this
> GL.
> "Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> schreef in bericht
> news:jlNEf.232245$XZ3.7544959@.phobos.telenet-ops.be...
is
>