Database Discussion Boards

Hello,

The following code returns the results we want.

However, they are being repeated.

For instance, we have ElectionName, Position, CandidateName and TotalCount.

Rather then display the results in the following format (current format)

SQL

ElectionName                    Position             Candidate Name  TotCount

Election Name 1 Mar 19 2014 	President	      Candidate Name 1	13
Election Name 1 Mar 19 2014 	President	      Candidate Name 2	22
Election Name 2 Mar 19 2014 	President	      Candidate Name 1	13
Election Name 1 Mar 19 2014 	President	      Candidate Name 2	22
Election Name 1 Mar 19 2014 	President	      Candidate Name 1	13
Election Name 1 Mar 19 2014 	President	      Candidate Name 2	22
Election Name 2 Mar 19 2014 	1ST Vice President    Candidate VP 1	27
Election Name 2 Mar 19 2014 	1ST Vice President    Candidate VP	8
Election Name 2 Mar 19 2014 	1ST Vice President    Candidate VP	27
Election Name 2 Mar 19 2014 	1ST Vice President    Candidate VP	8
Election Name 3 Mar 19 2014 	1ST Vice President    Candidate VP	27
etc
etc

We would like the results displayed in the following format:

SQL

ElectionName                    Position             Candidate Name  TotCount

Election Name 1 Mar 19 2014 	President	      Candidate Name 1	13
            		 	President	      Candidate Name 2	22
			 	President	      Candidate Name 1	13
			 	President	      Candidate Name 2	22
				President	      Candidate Name 1	13
			 	President	      Candidate Name 2	22
Election Name 1 Mar 19 2014 	1ST Vice President    Candidate VP 1	27
 				1ST Vice President    Candidate VP 1	8
				1ST Vice President    Candidate VP 1	27
 				1ST Vice President    Candidate VP 1	8
 				1ST Vice President    Candidate VP 1	27
 etc
 etc

with related positions group together with ElectioName.

Below is my code. Any help is greatly appreciated.

SQL

Select (e.ElectionName + CAST(e.ClosingDate AS VARCHAR(12))) as [ElectionName], 
p.position, 
c.candidateName,
COUNT(*) AS TotalCount
from ElectionResults er
inner join candidates c on er.candidateId = c.candidateid
inner join Positions p on er.positionId = p.positionId 
inner join Elections e on c.PositionId = c.PositionId
group by grouping Sets(e. ElectionName),p.position,p.positionid,c.candidateName, e.closingDate
order by p.PositionId

This can be achieved by looping through the resultset and clear the ElectionName if the Position is the same as in the previous row. But this doesn't belong to the database.

Wrong is evil and must be defeated.
- Jeff Ello[^]

As Jorgen has said, this is the wrong tool for the job, the database serves up the data, your UI/Report/Consumer of the data formats that data according you your requirements.

So do your loop/delete in the client, not the database.

Never underestimate the power of human stupidity
RAH

I was not needing for app. The code below is what I used for my app.

It is doing the grouping by electionName but I can't figure out how to count the total count for each candidate.

Can you guys add that bit?

I just to get the total scores for each candidateName

SQL

WITH
ctePreAgg AS
(
 SELECT (ElectionName + ' - ' + CAST(ClosingDate AS VARCHAR(12))) electionName, CASE WHEN Position='Member' THEN '' ELSE Position END As Position, CASE WHEN c.CurrentOfficeHolder='Incumbent' THEN CandidateName + '('+ c.CurrentOfficeHolder + ')' ELSE CandidateName END As CandidateName , c.PositionId,COUNT(*) TotalVotes 
   FROM Candidates c 
   JOIN Positions p ON c.PositionId = p.PositionId
   JOIN Elections e on c.ElectionId = e.ElectionId
  WHERE c.ElectionId IN (1,2,3)
  GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName, ClosingDate
)

  SELECT [OrgName] = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.Position)  ELSE mh.ElectionName END
        ,Names = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.CandidateName)    ELSE '' END
        ,PositionId    = CASE WHEN GROUPING(mh.PositionId) = 0 THEN mh.PositionId   ELSE '' END
        ,TotalCount = CASE WHEN GROUPING(mh.PositionId) = 0 THEN COUNT(*) ELSE '' END
  FROM  ctePreAgg mh
  GROUP BY ElectionName,PositionId WITH ROLLUP
  HAVING GROUPING(mh.ElectionName) = 0
  ORDER BY mh.ElectionName, GROUPING(mh.PositionId) DESC, mh.PositionID;

Well you are doing exactly what we advised you not to do and you want suggestion on how to improve doing that. Getting the total/count per group IS a database thing, formatting the text is NOT.

Personally I would use ROW_NUMBER and PARTITION OVER but not with a CTE.

Never underestimate the power of human stupidity
RAH

Ok, sorry for my stupidity but I thought this is a database forum.

If it is, I am only trying to figure out how to get Total Count for each candidate, not formatting request.

795]] Warning: Truncation may occur due to inserting data from data flow column "AS" with a length of 16 to database column "AS" with a length of 15.

I resolved this issue yesterday but today it failed again.

The package is successful if I run from Microsoft Visual studio. However, if I run initiate the job through SQL job agent it will fail.

Does anyone have a better idea to resolve this?

Please do not re-post - it will not get you a quicker or better answer, but may piss off some of the CPians around us Smile | :)

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Thank you for your observation.

You're more than welcome..

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

And when you run it using the different methods do you
- Use EXACTLY the same data. How do you know?
- Use EXACTLY the same database? How did you verify it?

One of the issues I have run across over time is in the data itself. If your source is a legacy system then there may be a crlf issue, it can be either 1 or 2 characters. They sometimes use weird row terminators as well.

I would check if AS is the last field in a row and then pull the data into a string and check the length of the field. VS may well treat line feeds differently than SSIS.

Never underestimate the power of human stupidity
RAH

[795]] Warning: Truncation may occur due to inserting data from data flow column "AS" with a length of 16 to database column "AS" with a length of 15.

I resolved this issue yesterday but today it failed again.

The package is successful if I run from Microsoft Visual studio. However, if I run initiate the job through SQL job agent it will fail.

Does anyone have a better idea to resolve this?

I took Jorgen's advice and modified my Movie database

I split the table into 2, the first table being movie information and the 2nd table being FLV information

So I can do the JOINs in displaying the data, and using it to create a Flash Movie Player,

But it just dawned on me that I have no clue on how to insert and update records for my new setup here.

So I came up with this.

My question, am I on the right track here, Inserting the first table, getting the ID of the record so I can use the ID in the 2nd table?

Or is there a better way in which I can insert in one shot?
I'm not ready to write stored procedures yet, in case that is suggested.

INSERT INTO MovieInfo(
   MovieName, MovieType, MoviePath, MoviePostage, MovieThumbnail, flv, h264
)
VALUES(
   @MovieName, @MovieType, @MoviePath, @MoviePostage, @MovieThumbnail, @flv, @H264
);
DECLARE @mID AS INT;
SET @mID = (
   SELECT MovieID FROM MovieInfo WHERE MovieName=@MovieName
)
INSERT INTO MovieInfo_flv(
 movieID, flv_movieName, flv_skin, flv_folderpath, flv_filename
)
VALUES(
   @mID, @flv_movieName, @flv_skin, @flv_folderpath, @flv_filename
)

modified 17-Mar-14 18:07pm.

You either do this as a 2 statement operation or you create a stored procedure - the SP path is by far the simplest an most robust.

I presume you have an IDENTITY field the Movie table. So when a record is inserted the system puts the new ID into a variable called @Scope_Identity you can get the value from there.

I used stored procs for EVERYTHING, the UI/Service NEVER talks directly to a table/view, so I have a code generator to build my CRUD procs. So I am not going to be able to help with string inserts Blush | :O

Never underestimate the power of human stupidity
RAH

Well thanks for the confirmation on that. I guess I'll take a stab on stored procedures then.

So here is some pseudo code for that.

Declare incoming variables
Insert into student table
Get Scope_Identity into a variable
Check for existing course - this may be done in the client
Insert missing course
Get Scope_Identity into another variable
Insert variables into link table (this is a many to many table linking the course and the student records)
Return the student record - I do this so the client has all the information about the student including the ID value.

Never underestimate the power of human stupidity
RAH

OK
I can swap out the words like student on that

I'll write one and post it to see how I did on it.

Thanks!

How do I structure my database tables so that it can allow many users to login simultaneously in my chat Application. While testing it in my IE and Crome in my PC.
Thanks.

What is it about the structure of the database that would prevent many users from logging in simultaneously? What problem are you facing?

The difficult we do right away...
...the impossible takes slightly longer.

I'm creating a chat App that will run online its includes signalR for real time messages. So now I'm trying to check if its can allow multiple users to log in in the in the same time using two browsers on my computer. when I check that I found that its override the first user that has being logged in first.

sbangani wrote:
How do I structure my database tables so that it can allow many users to login simultaneously

A database-server is designed to cater to multiple clients.

Simply do not use a desktop database (like MSAccess) and it should work out-of-the-box.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

I doubt you can design a database that doesn't allow multiple users.

You can certainly design/implement an application that uses a database which precludes that.

Hi All,
I want to get the result according to comma seperated value in field. First let me explain the table and their data

I have one table which contain following fields and data

file sequence || Cat_Id || Sequence

4,1,5,8,9,10,20 || 41 || 2
18,17,19,24,25 || 48 || 9
19,21,22,23,24,25,26 || 49 || 10

above is the sample data structure which contains the data in above format. Now I want to create a query which rerurn the value of file_sequence field in following format

4,1,5,8,9,10,20,18,17,19,24,25,21,22,26

Here one thing Which I want to notify
1. above table is result of a query which is sorted on the base of Sequence
2. Now we pick the result of field_sequence from the start in the same order.
3. now we pick the 4,1,5,8,9,10,20 then 18,17,19,24,25 and then 21,22,26
4. duplicate value removed (in above example 19,24,25 are duplicate)

so in this way I want to get the comma seperated value
4,1,5,8,9,10,20,18,17,19,24,25,21,22,26

Please suggest/guide how can i achieve this result

rup28aug@yahoo.co.in
Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

Company - ISOL, India

Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Maybe not the answer you're looking for, but your table doesn't even conform to the first normal form.
Normalize it, for example like this:

SQL

CREATE TABLE MyTable
	([fileid] int, [filesequence] int, [catid] int, [catsequence] int)
;
	
INSERT INTO MyTable
	([fileid], [filesequence], [catid], [catsequence])
VALUES
	(4, 1, 41, 2),
	(1, 2, 41, 2),
	(5, 3, 41, 2),
	(8, 4, 41, 2),
	(9, 5, 41, 2),
	(10, 6, 41, 2),
	(20, 7, 41, 2),
	(18, 1, 48, 9),
	(17, 2, 48, 9),
	(19, 3, 48, 9),
	(24, 4, 48, 9),
	(25, 5, 48, 9),
	(19, 1, 49, 10),
	(21, 2, 49, 10),
	(22, 3, 49, 10),
	(23, 4, 49, 10),
	(24, 5, 49, 10),
	(25, 6, 49, 10),
	(26, 7, 49, 10)
;

Now you can Query it like this:

SQL

select  fileid
from    mytable
group by fileid
order by min(catsequence),min(filesequence)

And the result will look like this:

It's easy enough to make that into a comma separated file if you would really need to.
But that task actually belongs to another layer than the database.

Wrong is evil and must be defeated.
- Jeff Ello[^]