January 28, 2008 at 9:36 am
Hi all,
I am no good in sql queries and needed some help regarding the same.
i have a table with the following columns.
tablename: moviesviewed
columns:
userid
secondsused
userhistoryid (primarykey)
filename
now i need to retrieve all the userhistoryids for a specific user (userid) where the sum of the secondsused (grouped by userid) is less than 100 ordered by the userhistoryid.
ill put it in a query format for better understanding of the requirement but as anyone can see the query will not work...
select userhistoryid from moviesviewed group by userid order by userhistoryid asc having sum(secondsused)<=100
any help is appreciated.
January 28, 2008 at 10:59 am
You need to get the required userid's and then use them with a derived table or a subquery.
Here is an example of a derived table:
SELECT M.*
FROM moviesviewed M
    JOIN
    (
        SELECT M1.userid
        FROM moviesviewed M1
        GROUP BY M1.userid
        HAVING SUM(M1.secondsused) < = 100
    ) D
        ON M.userid = D.userid
ORDER BY userhistoryid
January 29, 2008 at 12:11 am
yeh but here ill get all the userhistoryids for a specific user whose secondsused is more than 100. but i need only the userhistoryids for a user which totals to less than or equal to 100.
eg. for a userid 10 if the data is such...
userhistoryid userid secondsused
100 1 10
101 1 50
102 1 30
103 1 50
here only 100 101 and 102 should show up but according to the above query all 4 ids will show up......
January 29, 2008 at 2:48 am
Umm.... You obviously did not try my original query as no rows would be returned.
In future, please read the following when posting:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
As you seem to want a rolling total, try something like:
-- *** Test Data ***
DECLARE @m TABLE
(
    userhistoryid int NOT NULL
    ,userid int NOT NULL
    ,secondsused int NOT NULL
)
INSERT INTO @m
SELECT 100, 1, 10 UNION ALL
SELECT 101, 1, 50 UNION ALL
SELECT 102, 1, 30 UNION ALL
SELECT 103, 1, 50
-- *** End Test Data ***
-- Original Query - NO rows returned!
SELECT M.*
FROM @m M
    JOIN
    (
        SELECT M1.userid
        FROM @m M1
     GROUP BY M1.userid
HAVING SUM(M1.secondsused) < = 100
    ) D
    ON M.userid = D.userid
ORDER BY userhistoryid
-- Rolling total
SELECT M2.userhistoryid
FROM @m M1
    JOIN @m M2
        ON M1.userid = M2.userid
            AND M1.userhistoryid <= M2.userhistoryid
GROUP BY M2.userhistoryid
HAVING SUM(M1.secondsused) <= 100
January 29, 2008 at 4:26 am
hi ken,
sry bout the way ive put the up the post.. this is the first time im posting on this site and was unaware of the best practices. i looked through the link u send me thanks a lot.
however i tried the query and still this does not satisfy my requirement. as u can see in the query due to the group by userhistoryid this only returns the top one userhistory for each user. a group by the userid is required for the sum(secondsused) and not the userhistoryid. however using the group by(userid) will not allow the individual userhistoryids to be returned which is what i need. here are more details on the problem....
--Table Create Query
CREATE TABLE [dbo].[PayPerMinuteUserHistory](
[UserID] [int] NOT NULL,
[SecondsUsed] [int] NULL,
[FileName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserHistoryID] [int] IDENTITY(100,1) NOT NULL,
CONSTRAINT [PK_PayPerMinuteUserHistory] PRIMARY KEY CLUSTERED
(
[UserHistoryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--insert data queries
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,12,1000)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,15,1001)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,30,1002)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,19,1003)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,20,1004)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,15,1005)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,50,1006)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(100,60,1007)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(101,10,1008)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(101,50,1009)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(101,10,1010)
insert into table [dbo].[PayPerMinuteUserHistory]([userid],secondsused],
[UserHistoryID] ) values(101,50,1011)
here the query must return userhistoryids for each user till the total of teh seconds used is less than 100 so with the test data it must return 1000,1001,1002,1003,1004,1008,1009,1010
January 29, 2008 at 8:02 am
If you check my Rolling Total query, you will see I am grouping by M2.userhistoryid
January 30, 2008 at 8:36 am
hi ken,
the query worked... really nice and simple query... thanks a lot!!!!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy