help with Select statement

Hi!

This is my table:

CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)

This is some values:

INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)

As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.

Please help.

Best regards,
Zvonkoselect sifrob,katbroj,sum(kol) as kol
from Query_Result
group by sifrob,katbroj

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Zvonko wrote:

Quote:

Originally Posted by

Hi!
>
This is my table:
>
CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)
>
>
This is some values:
>
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)
>
As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.
>
Please help.
>
Best regards,
Zvonko

Help with SELECT please

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

>> I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

> I need to pull IMClinicDay for the date given
> in IMClinicDateFirst. For example, if my data looks like this:
> SSN: 999999999
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> then I need to pull what the IMClinicDay would be for this SSN in
> September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> > IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> > "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> > data in mm/dd/yy format.
> No, they are declared as datetime, which means that they are in a
> binary format. If you say
> SELECT * FROM tbl WHERE datecol = '07/01/05'
> You could get rows from from 2007-01-05, 2005-01-07 or any other
> of the six possible permutations, depending on the current settings.
> On the other hand:
> SELECT * FROM tbl WHERE datecol = '20070105'
> will always give the same set of data.
> OK, so that is not what you asked about, but since you had an apparent
> misunderstanding about datetime, I figured I should point it out.
> > I need to pull IMClinicDay for the date given
> > in IMClinicDateFirst. For example, if my data looks like this:
> > SSN: 999999999
> > MonthName: July
> > IMClinicDay: Monday PM
> > IMClinicDateLast: 07/01/05
> > IMClinicDateFirst: 09/01/05
> > then I need to pull what the IMClinicDay would be for this SSN in
> > September.
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:
> Stop using that silly redundant "tbl-" prefix; If nobody told you
> yet, SQL only has one data structure. Then you might want to read a
> basic book on data modeling - you always name a data element for what
> it is, not for how it is stored, where it is used, etc.
> Next, we need keys to have proper tables. An IDENTITY is **never** a
> key by definition. SSN is never VARCHAR() but it is fixed length, so
> all you did was invite a loss of data integrity. Ditto when you pulled
> oversize numbers out of the air for the other column sizes. Since there
> are no non-NOT NULL columns, you cannot ever have a key!! Think about
> VARCHAR(1) and what it means.
> Do you know of a month name that is CHAR(15)? Why are you using
> strings for temporal data in SQL? Why are you using vague strings like
> "Monday PM" for temporal data? Why did you violate ISO-8601 formats
> for the bad dates?
> What does the resident's academic year have to do with rotations?
> The whole mess looks denormalized. Just based on a few decades of
> prior experience, I would guess this ought o reduce down to something
> like this:
> CREATE TABLE ResidentRotations
> (ssn VARCHAR(9) NOT NULL
> REFERENCES Residents(ssn),
> resident_program VARCHAR(20) NOT NULL,
> rotation_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> rotation_start_time DATETIME NOT NULL,
> rotation_end_time DATETIME NOT NULL,
> CHECK (rotation_start_time < rotation_end_time),
> call_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> PRIMARY KEY (ssn, rotation_start_time));
> >> I need to pull what the IMClinicDay would be for this SSN in September. <<
> You need to use a Calendar table and insert the scheduled shifts in
> advance for the known duration. You can take care of holidays,
> re-scheduling, etc. with this approach.
> You might want to read Rick Snodgrass' s book on Temporal queries in
> SQL after you get thru a basic data modeling book and a few ISO
> standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> Here's a sample of the data I have:
> SSN: 999999999 (first row)
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> SSN: 999999999 (nth row)
> MonthName: September
> IMClinicDay: Wednesday PM
> IMClinicDateLast: 09/01/05
> IMClinicDateFirst: 10/01/05
> With a SELECT statement, I want to return all of the first row and only
> the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
> in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @.yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @.yearmonth + '01')

I assume that @.yearmonth holds the month you are looking for on the
form YYYYMM.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx