total due query help

[andydeans]

Hi,

I got some great help on the forum to create my total due query. However it produces it with just total due, how could i have it by month?

each table has a DueDate column which i would like to show on the query along with the users FullName from the users table which would join via clients table.

The existing query is this:

select sum(sub_amt) total_due from ( SELECT sum(AmountGI)  sub_amt
FROM generalinsurance
WHERE DatePaidGI is null union select sum(amount)   sub_amt from protection where DatePaid is null union select sum(amount)   sub_amt from buytolet where DatePaid is null union select sum(amount)   sub_amt from mortgage where DatePaid is null union select sum(Commission)   sub_amt from referrals where DatePaidReferral is null union select sum(Commission)   sub_amt from overseas where DatePaidOverseas is null ) a

if anyone could help with this i would appreciate this.

Thanks

[andydeans]

When i echo sub_amt on my page it does not work, yet when i test the query it comes back with total_due in the query result.

How can i display total_due instead of sub_amt

thanks

[tesuji]

well, it might be very helpful to know all related tables and all properly set-up primary and foreign keys. Without these pieces of information it's rather impossible to help you seriously (but pretty kind of stroking the crystal ball).

-- tesu

[andydeans]

Thanks, the code for referrals table is:

CREATE TABLE `referrals` (
  `ReferralID` int(11) NOT NULL auto_increment,
  `RefNumber` varchar(45) default NULL,
  `Type` varchar(45) default NULL,
  `DateReferredReferral` date default NULL,
  `IssueDateReferral` date default NULL,
  `DatePaidReferral` date default NULL,
  `Commission` decimal(14,2) default NULL,
  `ReferredTo` varchar(45) default NULL,
  `clients_ClientID` int(11) default NULL,
  `ReferralSold` enum('Yes','No') NOT NULL,
  `ReferralStatus` varchar(20) NOT NULL,
  PRIMARY KEY  (`ReferralID`),
  KEY `clients_referral` (`clients_ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Each other table has the same columns, i.e. clients_ClientID and DatePaid etc but just the name is maybe protection or mortgage as per query above.

My users table is:

CREATE TABLE `users` (
  `UserID` int(11) NOT NULL auto_increment,
  `FullName` varchar(75) default NULL,
  `FirstName` varchar(50) default NULL,
  `LastName` varchar(50) default NULL,
  `Email` varchar(500) default NULL,
  `Username` varchar(20) default NULL,
  `Password` varchar(20) default NULL,
  `AccessLevel` int(11) default NULL,
  PRIMARY KEY  (`UserID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

The users join clients table, the clients table has a FK ClientUserID which relates to users table and clients table has a PK of ClientID which is a FK in the products tables as clients_ClientID.

I would like to show the users name along with the clients name in the query.

Not sure how to put that into the existing query, also would be good if i could show the company name from each product table.

Many thanks for your help

[tesuji]

Hi,

here is some answer to your first question: "toatal due query: I got some great help on the forum to create my total due query. However it produces it with just total due, how could i have it by month?"

Answer (code not tested):
You can apply aggregate functions such as sum(), min(), avg() etc together with expressions or simple attributes of a table. For example, if you want to group various amounts by the month they accrued from together with the monthly subtotal you should code:

select monthname(datePaid) as Mois, sum(amount) as monthly_Mortgage from mortgage
  where DatePaid IS NULL  group by Mois;

Output could look like:
Mois_________monthly_Mortgage
January______3,541.58
February_______999.87
etc.

Important rule: All attributes appearing in the select statement which are not part of an aggregate function must be specified in the group-by clause, e.g.

select a, b, c, avg(d), min(e) from x where... group by a, b, c; -- correct clause
select a, b, avg(d), min(e) from x where... group by a; -- wrong clause, missing attribute b

(consider, there are about 30 useful aggregate functions)

Maybe you will get inspired by this group-by clause how to solve "I would like to show the users name along with the clients name in the query."? I'll noddle over this soon, and will be answering asap. To solve this task several tables must be joined together: referrals inner join clients inner join users, where "referrals" surrogates further tables.

Important: to do these joins efficiently it is important that all primary keys, and most important, all foreign keys be properly defined. If this requirement is not met, refferential integrity does not exist, also all attributes where the joins should be applied to must be explicitly itemized in an ON-clause, e.g. select ... from a join b ON a.attribute = b.attribute...

-- tesu

[tyson.crouch]

@tesu - You said that is important to group by all attributes which are not part of an aggregate function. Why exactly is this?? Its just that I haven't heard of this rule of thumb before.

Thx

[tesuji]

Hi tyson
I am glad to meet you here :) Let's given a table TuttiFrutti:

Fruits    	onStock		Location
--------------------------------------------
Apples          100			ABC
Oranges		300			BCA		
Plums		800			CBA
Oranges		200			BAC
Plums		100			CAB
Apples		150			ACB
...

Now consider these four select statements:

select fruits, locations, sum(onStock) from TuttiFrutti group by fruits, locations;
-- table TuttiFrutti will be produced completely for there is nothing to group by

select fruits, sum(onStock) from TuttiFrutti group by fruits; 
fruits		sum(onStock)
------------------------
Apples		250
Plums		900
Oranges		500

select sum(onStock) from TuttiFrutti;
sum(onStock)
------------
1650

select fruits, sum(onStock) from TuttiFrutti; --(won't never be executed on serious DBs)
-- Can not sum up apples and oranges?

Note btw, SQL-2003 Standard defined important expansions for group-by clause for doing OLAP, e.g. grouping sets, rollup, cube() which are very useful for creating complex queries.

I hope above examples do clarify something.

-- tesu

[andydeans]

Thanks for your help, I will await your help with the other queries if you can help i would appreciate it.

One thing, group by month. My column is set to Date type not day columns, months, years which i think would probably be better now looking at your query or do you think date type is ok?

Thank you again

[griswolf]

It is almost always better to use the column type that is 'natural'. In this case, consider using the "month" function: month(date_column)

[tesuji]

hi andydeans

date or timestamp datatype is quite usual. If you want to group by month, you can apply month() or monthname() function, as I already stated in:

SELECT MONTHNAME(datePaid) as Mois, sum(amount) as monthly_Mortgage FROM mortgage
      WHERE DatePaid IS NULL GROUP BY Mois;

Recording year, month, day separately isn't that a good idea for one usually deals with complete date, also compact date is easier to handle within a program. If one occasionally needs elements of date-type attributes, applying appropriate functions is efficient.

-- tesu

[andydeans]

Your brilliant, thank you for this one.

[andydeans]

Hi,

here is some answer to your first question: "toatal due query: I got some great help on the forum to create my total due query. However it produces it with just total due, how could i have it by month?"

Answer (code not tested):
You can apply aggregate functions such as sum(), min(), avg() etc together with expressions or simple attributes of a table. For example, if you want to group various amounts by the month they accrued from together with the monthly subtotal you should code:

select monthname(datePaid) as Mois, sum(amount) as monthly_Mortgage from mortgage
  where DatePaid IS NULL  group by Mois;

Output could look like:
Mois_________monthly_Mortgage
January______3,541.58
February_______999.87
etc.

Important rule: All attributes appearing in the select statement which are not part of an aggregate function must be specified in the group-by clause, e.g.

select a, b, c, avg(d), min(e) from x where... group by a, b, c; -- correct clause
select a, b, avg(d), min(e) from x where... group by a; -- wrong clause, missing attribute b

(consider, there are about 30 useful aggregate functions)

Maybe you will get inspired by this group-by clause how to solve "I would like to show the users name along with the clients name in the query."? I'll noddle over this soon, and will be answering asap. To solve this task several tables must be joined together: referrals inner join clients inner join users, where "referrals" surrogates further tables.

Important: to do these joins efficiently it is important that all primary keys, and most important, all foreign keys be properly defined. If this requirement is not met, refferential integrity does not exist, also all attributes where the joins should be applied to must be explicitly itemized in an ON-clause, e.g. select ... from a join b ON a.attribute = b.attribute...

-- tesu

Hey

tesu, have you had any joy to look at my query yet mate?

thank you again

[tesuji]

hi andydeans,

I didn't forget you. The reason why I didn't respond earlier is I haven't as yet accessed to a MySQL database, only MS SQL Server and Sybase at hand to date. So I couldn't test the following sql statements.

CREATE OR REPLACE VIEW sumsum( Product, Client, Firstname, Lastname, Total) AS
  SELECT 'generalinsurance', c.clientsName, u.FirstName, u.LastName, sum(p.AmountGI) as sub_amt 
   FROM users u, clients c, generalinsurance p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidGI IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'protection', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, protection p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'buytolet', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, buytolet p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'mortgage', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, mortgage p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'referrals', c.clientsName, u.FirstName, u.LastName, sum(p.Commission) as sub_amt 
   FROM users u, clients c, referrals p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidReferral IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'overseas', c.clientsName, u.FirstName, u.LastName, sum(p.Commission) as sub_amt 
   FROM users u, clients c, overseas p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidOverseas IS NULL
      GROUP BY c.clientsName, u.FirstName, u.LastName;

-- possible final selects on view sumsum:

-- Grand total/total due
select sum(Total) as 'total due' from sumsum;

-- Totals per product
select Product, sum(Total) as 'total due' from sumsum
  group by Product;
  
-- Totals per product and client
select Product, Client, sum(Total) as 'total due' from sumsum
  group by Product, Client;
  
-- Totals per Product, client, User
select Product, Client, Lastname, sum(Total) as 'total due' from sumsum
  group by Product, Client, Lastname;

I put the selects glued together with unions in a VIEW for easier handling. You can also put them in a STORED PROCEDURE which will then be called in the FROM clauses or even put them in the new WITH clause. As for the latter I am no that confident whether MySQL already supports new WITH clause. There would also be a further but more complex solution where the selects and unions be put in the final selects, I myself wouldn't prefer that.

Before creating the view you should test each select separately. There are also four final selects given using the created view. Possibly the last select where the grouping has been breakdowned till user's lastname won't show useful information in case of clients having only one user associated with.

I forgot to mention that the above problem could be superiorly and in a more efficient way solved with OLAP methods (grouping, rollup, cube etc).

-- tesu

[andydeans]

tesu,

what can i say.......thank you so much for all your help with this.

i really appreciate it.

i will let you know if all works ok once tested out.

Thanks again for your help.

[andydeans]

hi andydeans

date or timestamp datatype is quite usual. If you want to group by month, you can apply month() or monthname() function, as I already stated in:

SELECT MONTHNAME(datePaid) as Mois, sum(amount) as monthly_Mortgage FROM mortgage
      WHERE DatePaid IS NULL GROUP BY Mois;

Recording year, month, day separately isn't that a good idea for one usually deals with complete date, also compact date is easier to handle within a program. If one occasionally needs elements of date-type attributes, applying appropriate functions is efficient.

-- tesu

Hey Tesu,

thanks for your help with this one, it worked a treat, one thing though.

How would i join into the one query for al products, i.e. protection, mortgage, buytolet etc? and show the month it was due with the product type?

Each product has own table with same datepaid fields.

thank you again

[andydeans]

Just as a quick one Tesu, what are the advantages of using a view?

Is a view just a complex query made to look like a table?

thanks again

[andydeans]

Hi Tesu,

Trying to display how much commission is due from my query but having a problem here is my query:

CREATE OR REPLACE VIEW renewals( Product, ClientID, ClientFirstName, ClientLastName, ReviewDate, Commission) AS  SELECT 'generalinsurance', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDateGI, AmountGI 
FROM clients c, generalinsurance p      WHERE  c.ClientID = p.Clients_ClientID     AND ReviewDateGI >= CURRENT_DATE() AND ReviewDateGI <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDateGI <= CURRENT_DATE()      
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, amount UNION  SELECT 'protection', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate, amount   
FROM clients c, protection p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()     
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, amount UNION  SELECT 'buytolet', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate, amount    
FROM clients c, buytolet p      WHERE  c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()     
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, amount UNION  SELECT 'mortgage', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate, amount     
FROM clients c, mortgage p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()  
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, amount UNION  SELECT 'pensions_investments', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate, Commission   
FROM clients c, pensions_investments p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()    
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, Commission;

Could you help me in where i am going wrong?

thanks