SELECT	bersin_users.first_name, 
		bersin_users.last_name, 
		bersin_users.title, 
		bersin_users.email, 
		bersin_users.organization,
		--bersin_users_loginhistory.date_inserted, 
		bersin_users.uID, 
		SUM(bersin_users_loginhistory.[count]) AS TotalLogins
FROM bersin_users INNER JOIN bersin_users_loginhistory 
	ON bersin_users.uID = bersin_users_loginhistory.bersin_uid
WHERE (bersin_users_loginhistory.date_inserted >= @FromDate) 
	   AND (bersin_users_loginhistory.date_inserted <= @ToDate)
GROUP BY bersin_users.first_name, 
		 bersin_users.last_name, 
		 bersin_users.title, 
		 bersin_users.email, 
		 bersin_users.organization,
		-- bersin_users_loginhistory.date_inserted, 
		 bersin_users.uID
ORDER BY TotalLogins

quote:

---

wouldnt a select subquery perform this?

---

quote:

---

Why cant I run the query with the date_inserted column?

---

quote:

---

So I cant run a query which simply holds an aggregate count in a column

---

quote:

---

I have a column, count, which has a value of 1 for each record entered.

---

quote:

---

Select firstname, lastname, count(count) ....

---

Create Table #T (ID int identity(1,1) not null,letter char(1) not null,number int not null)

Insert #T (letter,number)
Select 'A',1 UNION ALL
Select 'A',1 UNION ALL
Select 'B',2 UNION ALL
Select 'C',3 UNION ALL
Select 'C',1


Select ID, letter, count(number)
FROM #t
Group by ID, letter
/*
1	A	1
2	A	1
3	B	1
4	C	1
5	C	1
*/
Select ID,letter, sum(number)
FROM #t
Group by ID, Letter
/*
1	A	1
2	A	1
3	B	2
4	C	3
5	C	1
*/


Select letter,count(number)
FROM #t
Group by letter
/*
A	2
B	1
C	2
*/


Select letter,sum(number)
FROM #t
Group by letter
/*
A	2
B	2
C	4
*/

Drop Table #t

quote:

---

Show me users name, email, company name, date of last login and the number of total logins for the user - this is an impossible query to run in t-sql?

John Smith john@smith.com Acme Inc. 03/08/2008 100

---

SELECT	bersin_users.first_name, 
		bersin_users.last_name, 
		bersin_users.title, 
		bersin_users.email, 
		bersin_users.organization,
		max(bersin_users_loginhistory.date_inserted) as LastLogin, 
		bersin_users.uID, 
		SUM(bersin_users_loginhistory.[count]) AS TotalLogins
FROM bersin_users INNER JOIN bersin_users_loginhistory 
	ON bersin_users.uID = bersin_users_loginhistory.bersin_uid
WHERE (bersin_users_loginhistory.date_inserted >= @FromDate) 
	   AND (bersin_users_loginhistory.date_inserted <= @ToDate)
GROUP BY bersin_users.first_name, 
		 bersin_users.last_name, 
		 bersin_users.title, 
		 bersin_users.email, 
		 bersin_users.organization,
		-- bersin_users_loginhistory.date_inserted, 
		 bersin_users.uID
ORDER BY TotalLogins

quote:

---

Originally posted by ljp099

Im trying to get a count of all user logins to display in a report - I have a column, count, which has a value of 1 for each record entered.


Select firstname, lastname, count(count) as TotalLogins Order by TotalLogins. But the count is always appearing as 1.

Report should look like:

John Smith 132
Jane Doe 101
Doris Day 99

---

quote:

---

Originally posted by ljp099

Heres the query I need help modifying - so that the last column in the query shows the number of Total Logins for a user:

SELECT bersin_users.first_name,
bersin_users.last_name,
bersin_users.title,
bersin_users.email,
bersin_users.organization,
--bersin_users_loginhistory.date_inserted,
bersin_users.uID,
SUM(bersin_users_loginhistory.[count]) AS TotalLogins
FROM bersin_users INNER JOIN bersin_users_loginhistory
ON bersin_users.uID = bersin_users_loginhistory.bersin_uid
WHERE (bersin_users_loginhistory.date_inserted >= @FromDate)
AND (bersin_users_loginhistory.date_inserted <= @ToDate)
GROUP BY bersin_users.first_name,
bersin_users.last_name,
bersin_users.title,
bersin_users.email,
bersin_users.organization,
-- bersin_users_loginhistory.date_inserted,
bersin_users.uID
ORDER BY TotalLogins

TotalLogins would show the total number of times a user has logged in (the sum of all their logins).

Thanks

---

SELECT bersin_users.uID,
       SUM(bersin_users_loginhistory.[count]) AS TotalLogins
FROM bersin_users INNER JOIN bersin_users_loginhistory
ON bersin_users.uID = bersin_users_loginhistory.bersin_uid
WHERE (bersin_users_loginhistory.date_inserted >= @FromDate)
AND (bersin_users_loginhistory.date_inserted <= @ToDate)
GROUP by bersin_users.uID
ORDER BY TotalLogins

quote:

---

Why cant I run the query with the date_inserted column?

---

quote:

---

Originally posted by blindman

The "count" columns of 1s is what Kimball advocates in his "datawarehouses for dummies" seminars.

---