Multiple Counts and a score - 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

General SQL Server Forums

New to SQL Server Programming

Multiple Counts and a score

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-02-17 : 12:52:43

Hi , as always I appreciate any help .. heres my scenario

I have a table called enablers , with the following data

title Raiser Assignedto
book Fred John
Apple Peter Peter
Orange Bill Roger
Cup John Fred

For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)

I am looking how to get the output like below
Name Total Points
Fred 2
Peter 1
Bill 1
John 2
Roger 1

Any help greatly appreciated as always :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-17 : 13:29:46

Here is one way:

DECLARE @Foo TABLE (title VARCHAR(50), Raiser VARCHAR(50), Assignedto VARCHAR(50))

INSERT @Foo
VALUES
('book', 'Fred', 'John'),
('Apple', 'Peter', 'Peter'),
('Orange', 'Bill', 'Roger'),
('Cup', 'John', 'Fred')

SELECT
	Name,
	SUM(Points) AS TotalPoints
FROM	
	(
		SELECT
			Raiser AS Name,
			COUNT(Raiser) AS Points
		FROM
			@Foo
		GROUP BY
			Raiser

		UNION ALL 

		SELECT
			AssignedTo AS Name,
			COUNT(AssignedTo)
		FROM
			@Foo
		WHERE
			CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
		GROUP BY
			AssignedTo
	) AS T
GROUP BY
	Name

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-02-18 : 04:09:56

Hi Lamprey thank you for the answer .. the table has over 500 lines of data , and the above data , book, Fred , John is fictious .. what do I need to enter for values ?? the column names ? title, raiser , assignedto ???

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-02-18 : 06:32:33

[code]Just you have to replace @Foo with your table name. In the Lamprey's SQL, @Foo holds temporary table data...

SELECT
Name,
SUM(Points) AS TotalPoints
FROM
(
SELECT
Raiser AS Name,
COUNT(Raiser) AS Points
FROM
~~@Foo~~ YourTableName
GROUP BY
Raiser

UNION ALL

SELECT
AssignedTo AS Name,
COUNT(AssignedTo)
FROM
~~@Foo~~ YourTableName
WHERE
CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
GROUP BY
AssignedTo
) AS T
GROUP BY
Name [/code]

--
Chandu

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-02-18 : 07:14:14

Hi Bandi , thanks I am getting somewhere now , just 2 more tweaks and I think I am there , forgot to add , that the data I have has another 2 columns called Type and CreateDate , so I need to trim the results to only ones created this year and then trim the results to have only Type='Kaizen' , I need to add a where statement for these but cannot see where in the code to add ... any help again really appreciated

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-02-18 : 07:46:38

[code]SELECT
Name,
SUM(Points) AS TotalPoints
FROM
(
SELECT
Raiser AS Name,
COUNT(Raiser) AS Points
FROM
@Foo YourTableName
WHERE Type='Kaizen' AND YEAR( CreateDate ) = YEAR(GETDATE())
GROUP BY
Raiser

UNION ALL

SELECT
AssignedTo AS Name,
COUNT(AssignedTo)
FROM
@Foo YourTableName
WHERE Type='Kaizen' AND YEAR( CreateDate ) = YEAR(GETDATE()) AND
CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
GROUP BY
AssignedTo
) AS T

GROUP BY
Name
[/code]

--
Chandu

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-02-18 : 09:20:41

Bandi ... many ,many thanks ... that's nearly there ... though I do receive an error message The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I guess its because my CreateDate is a char data type .... anyway to get around this ???

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2014-02-18 : 10:08:49

Either change your Createdate to a datetime (the best answer) Or convert your Char to a datetime in the query (will affect performance slightly).

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-02-20 : 03:35:45

If your CreateDate column data format is YYYY-MM-DD , you can do as follows:

CreateDate LIKE DATENAME( YYYY, getdate()) + '%'

The above code depends on CreateDate format...

Refer
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources