SQL Statement

[Little_Man22]

Hi All,

If someone could give me a hand with this SQL problem, I'd be most appreciative.

The following code works, but it gives me totals for the company instead of the total per club name (cName) which is what I want. I need to know how to make it do totals by club name. Any ideas?


SELECT C.cName AS Club, cost.A AS Total_Cost_of_Equipment, useCharge.B AS Total_Revenue, payments.C AS Total_Payments, Visits.D AS Total_Visits

FROM ( SELECT SUM(E.cost) A
FROM Equipment E ) AS cost,
( SELECT SUM(U.useCharge) B
FROM Uses U ) AS useCharge,
( SELECT SUM(P.amount) C
FROM Payment P, Member M, Club C

WHERE P.SIN = M.SIN AND M.homeClub = C.cName ) AS Payments,
( SELECT COUNT(V.SIN) D
FROM Visit V ) AS Visits,
Club C

/* Need to GROUP BY cName */



OUTPUT:



Club Total_Cost_of_Equipment Total_Revenue Total_Payments Total_Visits

Bluebell 609363.0 629.0 337.0 149

Daffodil 609363.0 629.0 337.0 149

Lupin 609363.0 629.0 337.0 149

Marigold 609363.0 629.0 337.0 149

Poppy 609363.0 629.0 337.0 149

Primrose 609363.0 629.0 337.0 149

 

[pdx_man]

A little convuluted at first look. First, what is your level of expertise in SQL?

How do these tables relate? Here are the tables that I identify:

Equipment
Uses
Payment
Member
Club
Visit

Now, I see that Payments are related to Members and Members are related to Clubs. (P.SIN = M.SIN AND M.homeClub = C.cName)

How do the others relate?

Have you defined the relationships in the Relationships window?

but it gives me totals for the company instead of the total per club name

Where\What is Company?

 

[Little_Man22]

My SQL is beginner level, and company = all the Clubs


I’ve written it another way, this may make it clearer. In this case I get all the results I want, but not in the format I want.

The format I want is to have the club names on the left, with the each of the following as columns along the top



SELECT E.cname AS Club, SUM(E.cost) AS Total_Cost_of_Equipment

FROM Equipment E

GROUP BY E.cName



SELECT U.cName AS Club, SUM(U.useCharge) AS Total_Revenue

FROM Uses U

GROUP BY U.cName



SELECT M.homeclub AS Club, SUM(P.amount) Total_Payments

FROM Payment P, Member M, Club C

WHERE P.SIN = M.SIN AND M.homeClub = C.cName

GROUP BY M.homeClub



SELECT V.cName AS Club, COUNT(V.SIN) AS Visits

FROM Visit V

GROUP BY V.cName



OUTPUT:



Club Total_Cost_of_Equipment

Bluebell 151171.0

Daffodil 118651.0

Lupin 104629.0

Marigold 8238.0

Poppy 61740.0

Primrose 164934.0



Club Total_Revenue

Bluebell 77.0

Daffodil 74.0

Lupin 217.0

Marigold 69.0

Poppy 71.0

Primrose 121.0



Club Total_Payments

Bluebell 31.0

Daffodil 38.0

Lupin 97.0

Marigold 38.0

Poppy 70.0

Primrose 63.0



Club Visits

Bluebell 24

Daffodil 17

Lupin 41

Marigold 14

Poppy 20

Primrose 33



Thanks,

-rjd.