Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Query

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 05:39:32

Hi I'm new to SQL and need some help. I've created a query to find the total amount of time someone spends on a sunbed by summing the individual visits. The query is shown below:

SELECT
CLIENTSESSION.CLIENTID,
SUM(CLIENTSESSION.DURATION) AS TOTAL_DURATION
FROM
CLIENTSESSION
GROUP BY
CLIENTSESSION.CLIENTID

I now want to be able to find all of those clients who's total duration is greater than 60 and then append these records into a new table.
Can anyone help??

Cheers

Paul

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-22 : 05:53:55

SELECT CLIENTSESSION.CLIENTID, SUM(CLIENTSESSION.DURATION) AS TOTAL_DURATION
INTO #NEW_TABLE
FROM
CLIENTSESSION
GROUP BY
CLIENTSESSION.CLIENTID
HAVING SUM(CLIENTSESSION.DURATION) > 60

Regards

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-22 : 05:54:09

INSERT INTO yourtable

SELECT
CLIENTSESSION.CLIENTID,
SUM(CLIENTSESSION.DURATION) AS TOTAL_DURATION
FROM
CLIENTSESSION

WHERE sum(clientsession.duration) > 60

GROUP BY
CLIENTSESSION.CLIENTID

-------
Moo.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 06:26:02

Hi,
I'm using crespo 24's method at the moment which works great, but I need to make it a bit more complicated i.e I need to pull info from another table to insert into a new table. My query at the moment looks like this:

SELECT
CLIENTSESSION_IMPORT.CLIENTID,
SUM(CLIENTSESSION_import.DURATION) AS TOTAL_DURATION,
CLIENT_IMPORT.TITLE,
CLIENT_IMPORT.FIRST_NAME,
CLIENT_IMPORT.MIDDLE_NAME,
CLIENT_IMPORT.LAST_NAME
FROM
CLIENTSESSION_IMPORT
INNER JOIN CLIENT_IMPORT ON (CLIENTSESSION_IMPORT.CLIENTID = CLIENT_IMPORT.CLIENTID)
GROUP BY
CLIENTSESSION_import.CLIENTID
having SUM(CLIENTSESSION_import.DURATION) > 60

but I am getting an "invalid column reference" error. I can't see anything wrong with the column names. I've also left out the INTO #NEW TABLE statement at the moment as I want to check its selecting the right criteria.

Cheers

Paul

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-22 : 06:31:18

I don't think it is the cause of your problem, but you will need to include your additional fields into the group by clause.

-------
Moo.

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-22 : 07:31:21

quote:

Hi,
I'm using crespo 24's method at the moment which works great, but I need to make it a bit more complicated i.e I need to pull info from another table to insert into a new table. My query at the moment looks like this:

SELECT
CLIENTSESSION_IMPORT.CLIENTID,
SUM(CLIENTSESSION_import.DURATION) AS TOTAL_DURATION,
CLIENT_IMPORT.TITLE,
CLIENT_IMPORT.FIRST_NAME,
CLIENT_IMPORT.MIDDLE_NAME,
CLIENT_IMPORT.LAST_NAME
FROM
CLIENTSESSION_IMPORT
INNER JOIN CLIENT_IMPORT ON (CLIENTSESSION_IMPORT.CLIENTID = CLIENT_IMPORT.CLIENTID)
GROUP BY
CLIENTSESSION_import.CLIENTID
having SUM(CLIENTSESSION_import.DURATION) > 60

but I am getting an "invalid column reference" error. I can't see anything wrong with the column names. I've also left out the INTO #NEW TABLE statement at the moment as I want to check its selecting the right criteria.

Cheers

Paul

Here you go

CREATE TABLE CLIENTSESSION (CLIENTID SMALLINT, DURATION NUMERIC(9,2))
INSERT INTO CLIENTSESSION (CLIENTID, DURATION)
SELECT '01', '20.00'
UNION ALL
SELECT '01', '35.00'
UNION ALL
SELECT '03', '25.00'
UNION ALL
SELECT '03', '50.00'
UNION ALL
SELECT '02', '10.00'
UNION ALL
SELECT '02', '30.00'
UNION ALL
SELECT '02', '30.00'

CREATE TABLE CLIENT(CLIENTID SMALLINT, SURNAME VARCHAR(50), FORENAMES VARCHAR(50))
INSERT INTO CLIENT(CLIENTID, SURNAME, FORENAMES)
SELECT '01', 'CRESPO', 'RIO'
UNION ALL
SELECT '02', 'NIGHTELF', 'CRESPO'
UNION ALL
SELECT '03', 'SAVIOLA', 'DIEGO'

SELECT A.CLIENTID,
SUM(A.DURATION) AS TOTAL_DURATION,
B.SURNAME,
B.FORENAMES
FROM CLIENTSESSION AS A
INNER JOIN CLIENT AS B
ON A.CLIENTID = B.CLIENTID
GROUP BY A.CLIENTID, B.SURNAME, B.FORENAMES
HAVING SUM(A.DURATION) > 60

DROP TABLE CLIENTSESSION
DROP TABLE CLIENT

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 07:32:01

Hi,
Mr Mist, what you suggested did cure the problem, but I now have another problem. I have now altered the sql above to include the insert statement and have added additional fields , now I thought that by using the group by clause this would find only one instance of the clientid. The problem is that when I go to insert the records I get an error stating that there is a violation of the primary key, as the table that the records are being inserted into is going to hold only one instance of the clientid.

My SQL is as follows:

INSERT INTO CLIENT (CLIENTID, FIELD_1, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME,
DOB, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ADDRESS5, ADDRESS6, POSTCODE,
HOME_TELEPHONE, WORK_TELEPHONE, MOBILE_TELEPHONE, EMAIL_ADDRESS, GENDER,
DOCTOR_NAME, DOCTOR_TELEPHONE, SESSIONDISCOUNT, COURSEDISCOUNT, PRODUCTDISCOUNT,
CREDIT_LIMIT, DO_NOT_MAIL, DATE_ENROLLED, BRANCH_ID,
SKIN_TYPE, SOURCE_ID, MEMBERSHIP_ID)
SELECT
CLIENTSESSION_IMPORT.CLIENTID,
SUM(CLIENTSESSION_IMPORT.DURATION) AS TOTAL_DURATION,
CLIENT_IMPORT.TITLE,
CLIENT_IMPORT.FIRST_NAME,
CLIENT_IMPORT.MIDDLE_NAME,
CLIENT_IMPORT.LAST_NAME,
CLIENT_IMPORT.DOB,
CLIENT_IMPORT.ADDRESS1,
CLIENT_IMPORT.ADDRESS2,
CLIENT_IMPORT.ADDRESS3,
CLIENT_IMPORT.ADDRESS4,
CLIENT_IMPORT.ADDRESS5,
CLIENT_IMPORT.ADDRESS6,
CLIENT_IMPORT.POSTCODE,
CLIENT_IMPORT.HOME_TELEPHONE,
CLIENT_IMPORT.WORK_TELEPHONE,
CLIENT_IMPORT.MOBILE_TELEPHONE,
CLIENT_IMPORT.EMAIL_ADDRESS,
CLIENT_IMPORT.GENDER,
CLIENT_IMPORT.DOCTOR_NAME,
CLIENT_IMPORT.DOCTOR_TELEPHONE,
CLIENT_IMPORT.SESSIONDISCOUNT,
CLIENT_IMPORT.COURSEDISCOUNT,
CLIENT_IMPORT.PRODUCTDISCOUNT,
CLIENT_IMPORT.CREDIT_LIMIT,
CLIENT_IMPORT.DO_NOT_MAIL,
CLIENT_IMPORT.DATE_ENROLLED,
CLIENT_IMPORT.BRANCH_ID,
CLIENT_IMPORT.SKIN_TYPE,
CLIENT_IMPORT.SOURCE_ID,
CLIENT_IMPORT.MEMBERSHIP_ID
FROM
CLIENTSESSION_IMPORT
INNER JOIN CLIENT_IMPORT ON (CLIENTSESSION_IMPORT.CLIENTID = CLIENT_IMPORT.CLIENTID)
GROUP BY
CLIENTSESSION_IMPORT.CLIENTID,
CLIENT_IMPORT.TITLE,
CLIENT_IMPORT.FIRST_NAME,
CLIENT_IMPORT.MIDDLE_NAME,
CLIENT_IMPORT.LAST_NAME,
CLIENT_IMPORT.DOB,
CLIENT_IMPORT.ADDRESS1,
CLIENT_IMPORT.ADDRESS2,
CLIENT_IMPORT.ADDRESS3,
CLIENT_IMPORT.ADDRESS4,
CLIENT_IMPORT.ADDRESS5,
CLIENT_IMPORT.ADDRESS6,
CLIENT_IMPORT.POSTCODE,
CLIENT_IMPORT.HOME_TELEPHONE,
CLIENT_IMPORT.WORK_TELEPHONE,
CLIENT_IMPORT.MOBILE_TELEPHONE,
CLIENT_IMPORT.EMAIL_ADDRESS,
CLIENT_IMPORT.GENDER,
CLIENT_IMPORT.DOCTOR_NAME,
CLIENT_IMPORT.DOCTOR_TELEPHONE,
CLIENT_IMPORT.SESSIONDISCOUNT,
CLIENT_IMPORT.COURSEDISCOUNT,
CLIENT_IMPORT.PRODUCTDISCOUNT,
CLIENT_IMPORT.CREDIT_LIMIT,
CLIENT_IMPORT.DO_NOT_MAIL,
CLIENT_IMPORT.DATE_ENROLLED,
CLIENT_IMPORT.BRANCH_ID,
CLIENT_IMPORT.SKIN_TYPE,
CLIENT_IMPORT.SOURCE_ID,
CLIENT_IMPORT.MEMBERSHIP_ID
having SUM(CLIENTSESSION_IMPORT.DURATION) > 60

Any help would be greatly appreciated as I think I am almost there!!!

Cheers

Paul

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-22 : 07:44:56

Check the data in your CLIENT_IMPORT table and check for duplicate IDs... that's where your problem is. It sounds like some members have more than one entry with different details.

Edited by - Crespo24 on 01/22/2003 07:45:28

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 07:56:32

Hi,

Sorry to be a pain, but how do I check for duplicate clientid's

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-22 : 08:26:36

quote:

Hi,

Sorry to be a pain, but how do I check for duplicate clientid's

SELECT CLIENTID
FROM CLIENT_IMPORT
GROUP BY CLIENTID
HAVING COUNT(*) > 1

if you get any rows returned then you have a problem!

Another way is this :

SELECT COUNT(*) FROM CLIENT

SELECT DISTINCT COUNT(*) FROM CLIENT

if they differ then you know you have duplicates, but stick to the first method though.

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-22 : 08:27:52

quote:

Hi,

Sorry to be a pain, but how do I check for duplicate clientid's

Select count(*), IMPORT.CLIENTID
from [whichevertabletheyarein]
having count(*) > 1

~~But, you are I think correct in your assumption that grouping on the ID would limit the output to one line per client, so maybe the problem is elsewhere.~~ Is the output table definitely empty?

EDIT because all of that goes out the window once you group on more than one thing. D'oh.
-------
Moo.

Edited by - mr_mist on 01/22/2003 08:46:01

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 08:41:57

Hi it was my fault, as I had left 1 record in the table I was trying to insert to so duplication would have occurred. With regards to the above query, can I just remove the group by clause to stop the grouping and will the query still work or will I have to make other ammendments?

Cheers

Paul

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-22 : 08:48:14

You can't remove the grouping because you have a sum in there for the client session length. You'd only be able to remove the grouping if you somehow linked to this information in another manner. You may find it easier to go about it in two stages if you can't get it to work in one.

Populate your output table with client details then add in the total session times, or something.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources