Help for Group By. - 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

Help for Group By.

Author

Topic

hallyvaux
Starting Member

12 Posts

Posted - 2011-05-02 : 20:26:43

I posted select statements I used for a report for my assignment earlier and now I need make it so it shows profit percentages by city. I'll post what I have then how I'm trying to group it. I'm super sorry. I know this is so messy and awful but I swear I'm trying to get it.
Anyway:

SELECT ORDID "Order ID", NAME, ADDRESS, SELLPRICE "Price Sold", COSTPRICE "Product Cost", ROUND ((COSTPRICE / SELLPRICE) * 100) "% Profit"
FROM GS_CUSTOMER, GS_SALES
WHERE GS_CUSTOMER.CUSTID = GS_SALES.CUSTID
ORDER BY ORDID;

Ok so I dont need the order id,name or address but since I need to group by city, I think I would add my cityid to the select statement.
I've actually tried to add SUM before Round then tried Group By on City ID? I have had a bit of success using and understanding group by but it all seems to be gone from my brain.
You have to have aggregate functions to even use group by right?

I tried this and I knew as I was doing it, it was way off but I just can't wrap my mind around how to do this. Im thinking it requires something more complicated.

SELECT CITYID SELLPRICE "Price Sold", COSTPRICE "Product Cost", ROUND(SUM ((COSTPRICE / SELLPRICE) * 100)) "% Profit"
FROM GS_CUSTOMER, GS_SALES
WHERE GS_CUSTOMER.CUSTID = GS_SALES.CUSTID
GROUP BY CITYID;

I'm sorry about my onslaught of questions but I am coming dangerously close smashing this computer into a million pieces as I've been working on this assignment for hours.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 20:49:58

I think it would become much easier if you understand couple of things about grouping. For a moment, forget everything and think that you have only two columns in your table - Customer ID and Quantity. So your data is something like this:


Customer ID   Quantity
   1              3
   2              5
   1              7
   2              10

So you sold a total of 25 items to two customers. If you just wanted to know how many items you sold, you would write a query like this:

select sum(Quantity) from YourTable

That will give you the answer as 25.

But now what if you wanted to know how much you sold to each customer? Then, your query would be very similar to the previous one, except, you would group by customerId - like this

select sum(Quantity) from YourTable group by CustomerId

That would give you two rows - 10 and 15.

But that doesn't tell you whom you sold 10 to and whom you sold 15 to. So you would change the query again like this:

select CustomerId, sum(Quantity) from YourTable group by CustomerId

Now you have each customerID listed along with how much you sold to each.

The only other thing you need to remember to get the basics correctly is that when you use aggregate functions such as SUM, any other column you have in the select list that is outside of the aggregate function MUST be listed in the group by clause. So, you CANNOT do the following:

select CustomerID, SUM(Quantity) from YourTable

That is basically it! Look up the following tutorials and spend a few minutes trying to understand those:
http://www.w3schools.com/sql/sql_groupby.asp
http://www.academictutorials.com/sql/sql-group-by.asp

I know I haven't answered your question. I am sure someone else will. I just like to get on a soap box and preach

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 21:51:13

Just so I don't come across as someone who lectures and then goes away: :)

Your question was about how to calculate profit percentages by city. So, first think about how you will calculate profit.

profit = sellprice - costprice

Then, profit percentage is


profit_percentage = (sellprice-costprice)*100.0/costprice

That is for a single item. If you sold a lot of different things, your profit percentage would be:

(sum(sellprice)-sum(costprice))*100.0/sum(costprice)

You are just adding up how much money you made by selling all that stuff, subtracting the sum of all the monies you paid out and then calculating that profit as a percentage of your cost.

So your SQL statement would be


select (sum(sellprice)-sum(costprice))*100.0/sum(costprice) from GS_SALES

Now that is the total profit percentage. What if you wanted to find the profit percentage by city? (Which is really what your question was). Just as I said in my previous post, it should be as simple as:


select 
   City, 
   (sum(sellprice)-sum(costprice))*100.0/sum(costprice) as ProfitPercentage
from 
   GS_SALES 
group by 
   City

But there is just one slight problem. GS_SALES just does not have the City as a column. So you need to join the customer table which has the column City. And that would be


select
   City,
   sum(sellprice)-sum(costprice))*100.0/sum(costprice) as ProfitPercentage
from
   GS_SALES, GS_CUSTOMER
where
   GS_CUSTOMER.CUSTID = GS_SALES.CUSTID
group by
   City

That should get you the answer. Now if you wanted to add other columns, or other sums, you can - but just remember the rules about grouping that I said earlier. You can round if you like, or you can choose not to. But, first get this right. Everything else can be built up on top of this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources