Sql vb.net Query Select field Total along with percent total grouped by field

Question

0.00/5 (No votes)

See more:

I am having a sql problem and I want to see if anyone knows a solution.
I am attempting to pull data from sql, put it into a datagrid view, then print the data into an excel spread sheet.

Let me explain where i'm having trouble. The data must be pulled from sql already formatted, so that it goes into the data grid view then to excel.

I totally understand how to get the data out, put it into a datagrid view and then print to a formatted excel sheet.

Here is the problem:

I need the data grid view to look like the following:

Person name \ employee number \ Column 1 total \ column 1 total percent of highest value of column 1

There are a total 5 columns each with percentages.

Here is what i have so far, I apologize that it will run on and not be formatted to be read correctly, I will do my best.

Here is the sql statement I have and its not doing what I need it to do:
---------------------

SQL

 Declare @MaxVar1 int
  Declare @MaxVar2 int
  Declare @MaxVar3 int
  Declare @MaxVar4 int
  Declare @MaxVar5 int
 SELECT @MaxVar1 = Count(Column1) FROM dbo.Mytable WHERE Condition1 = 'VbCondition1' AND Condition2 = 'VbConditon2' GROUP BY EmployyeNum ORDER BY COUNT(Column1)

SELECT NAME, EmployeeNum, SUM(Column1), (Sum(Column1) *100 / @MaxVar1
From dbo.MyTable Where Condition1 = 'Vbcondtion1' AND Condition2 = 'VbCondition2'
Group By Name, EmployeeNum Order by @MaxVar1 Desc

-------------------

What i'm getting is the highest input for the column and not a computed total for the employee for a column. When I divide the Sum by the MaxVar I can not get the total for the employee and show the percent that the employee completed.

SQL is not letting me do a subquery inside of a query that gets a value.

Is there some other way to do this?

Any help or advice will be more than appreciated.

Posted 21-Dec-11 7:28am

Member 8182832

Updated 21-Dec-11 8:27am

Wendelius

v2

Add a Solution

2 solutions

Solution 1

So isn't this working?

SQL

SELECT Name, 
       EmployeeNum, 
       SUM(Column1), 
       SUM(Column1) *100 / COUNT(*)
FROM dbo.MyTable 
WHERE Condition1 = 'Vbcondtion1&' 
AND Condition2 = 'VbCondition2'
GROUP BY Name, EmployeeNum

That should get the sum of Column 1 and on the second column the sum is divided by the number of rows for the employee.

Posted 21-Dec-11 8:34am

Wendelius

Comments

Member 8182832 21-Dec-11 14:56pm

Not exactly. maybe I didn't explain it well.

Lets say an employee sells 3 products today and then sells 4 tomorrow and has a total of 7 for the week. (so 2 entries for this employee in sql.)

now another employ sells 2 today and 1 tomorrow. (again two entries)

The largest amount of sales for all employees is a total of 7. (if i add up all the columns then it would be a total of 10 which is not what i am looking for. Not trying to find the percent of the total, only the percent of the highest number grouped by an employee.)

so the output table should be
employee 1 -Name, 7 sales, 100percent
employee 2 - Name, 3 sales, 23percent

and so on for all employees...comparing their sales to the largest amount of sales which is 7 for one employee between specific dates.

I simplified it a great deal. There are other variables based of which store and what shift the employee works and filters accordingly based on "vbCondition"

Thanks for submitting some code. I tried it out and the results were skewed and didn't show accurate percentages.
The code above I wrote works, but only uses the largest 1 entry. I have to find the Largest Total by one employee and then use that to figure out the persons percentage.

This is very frustrating...

Wendelius 21-Dec-11 15:11pm

And the Column1 is the amount of sales per day per employee? What is the column defining the date?

Member 8182832 21-Dec-11 15:26pm

Correct column1(I used column 1 as an example but actually its column number 3 in my table) is the persons total sales for the day. My fields in the table are such:

Name, employeeNum, NumOfSales, DateWorked, Shift, Store, DateSubmitted

I need to add up each employees total sales for each day, then find the largest number of sales, then find every employees percentage of the highest persons sales.

Member 8182832 21-Dec-11 15:27pm

Thanks for your help on this. I appreciate your time.

Wendelius 21-Dec-11 15:31pm

Don't thank yet, let's first see if we can get it working :)

Member 8182832 21-Dec-11 15:33pm

The other issue is that I need to be able to also find the percentages of the other columns as well.

Some of the other columns are as follows:

NumOfSales, Sales%, NumOfCalls, Calls%, NumReferals, Referral% and so on.

Each % column must be based on the percentage of the employee with the largest number in that column. It would be much easier if each employee only had one entry, but each employee gets entered in everyday so each employee has 20 entries a month.

Member 8182832 21-Dec-11 15:22pm

Basically I just need to do
COUNT(MAX(SALES)) group By Employee --But that doesn't work

But sql wont let you do a sub-query inside of a query and I have no idea where to go from here, I have tried google and it doesn't seem that anyone knows how to do this all in one sql statement or (SP)

Wendelius 21-Dec-11 15:54pm

Actually you can do subqueries inside a query. One variation is to use an inline view. Would something like this give you necessary elements to do the calculations:

SELECT Name,
EmployeeNum,
b.MaxSales
FROM dbo.MyTable,
( SELECT MAX( a.Sum1) MaxSales
FROM ( SELECT SUM(NumOfSales) Sum1
FROM dbo.MyTable
GROUP BY EmployeeNum ) a ) b
WHERE Condition1 = 'Vbcondtion1&'
AND Condition2 = 'VbCondition2'

Member 8182832 21-Dec-11 16:21pm

I'm going to play with this idea and see if there is some other way to simplify the process. Thanks for your input!

Wendelius 21-Dec-11 16:25pm

No problem :) The reason I'm suggesting this is that using a single statement is most likely perform much better compared to procedural approach.

Member 8182832 21-Dec-11 16:08pm

Im not sure how I would use that. When I plugged in the above query I got the total sales in the table.

Although I did figure out how to do what I need it to do with the help of a fellow employee.

im going to add the answer so that if anyone else needs to do this in the future they will know how to do it.

Wendelius 21-Dec-11 16:21pm

DId you get the total sales or max sales for an employee. So what I'm thinking of is something like:
NAME, EmployeeNum, SUM(Sales), (Sum(Sales) *100 / @MaxSales
SELECT Name,
EmployeeNum,
SUM(Sales)
SUM(Sales) * 100 / b.MaxSales
FROM dbo.MyTable,
( SELECT MAX( a.Sum1) MaxSales
FROM ( SELECT SUM(NumOfSales) Sum1
FROM dbo.MyTable
GROUP BY EmployeeNum ) a ) b
WHERE Condition1 = 'Vbcondtion1&'
AND Condition2 = 'VbCondition2'
GROUP BY Name,
EmployeeNum

Member 8182832 21-Dec-11 16:28pm

I'm going to have to play around with it and try and figure out the easiest way to turn this long complicated query into a stored procedure and call that from .NEt

I will try your above query tomorrow and see if i can pull the max from the employees totals and then get the percentage.

The sad thing is you can actually do this on paper with a pencil in about 5 mins, but the bosses dont like math :-) haha

Thanks for your input and ideas.

Wendelius 21-Dec-11 16:37pm

You're welcome :) It would be nice to hear how it turned out.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 8182832 · Accepted Answer · 2011-12-21T10:16:00

The answer is to use a temp table.

code is below:
---------------

SQL

Declare @MaxSales int

SELECT EmployeeNum, SUM(Sales) as SalesTmp
  into #tempSales
  FROM [dbo].[MyTable]
  where Condition = 'Condition1' 
  group by EmployeeNum
  order by EmployeeNum

SELECT @MaxSales = MAX(SalesTmp) from #TempSales

SELECT NAME, EmployeeNum, SUM(Sales), (Sum(Sales) *100 / @MaxSales
From dbo.MyTable Where Condition1 = 'Vbcondtion1' AND Condition2 = 'VbCondition2'
Group By Name, EmployeeNum Order by @MaxSales Desc

DROP TABLE #TempSales

----------------------

this is one solution to do this.

If anyone knows a better way to do this please post your answer.
I hope this will help someone else too. This was a huge pain to figure out.