Sub total and grand total

Question

1.00/5 (1 vote)

See more:

i have table named Customer.
and it has CustomerId , CusName, GroupID, CreditAllowed
             1           fernando   1         10
             2           perera     2          5
             3           anne       1          4
             4           mark       2         10
             5           john       2          4

i want to group by this according to GroupID and i want to get sub total and grand total of creditAllowed column according to GroupID

What I have tried:

i try it with mysql but when i put data table for pagination it didnt get correct. i want to try this in php language

$ql = mysqli_query($connection,"SELECT Customer.CustomerID,Customer.CusName,CusGroup.groupId,Customer.CreditAllowed, SUM(Customer.CreditAllowed) AS total FROM Customer LEFT JOIN CusGroup ON Customer.GroupID=CusGroup.groupId GROUP BY CusGroup.groupId ,Customer.CustomerID with ROLLUP");

Posted 27-Apr-17 17:02pm

Member 13143008

Updated 27-Apr-17 19:33pm

v2

Add a Solution

1 solution

Add a Solution

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

Varun Sareen · Answer 1 · 2017-04-27T19:33:00

Solution 1

Dear Friend,

Try this:-

Select GroupID,SUM(CreditAllowed) [Sub Total],T.[Grand Total] 
from Customer 
Outer apply
(
	Select SUM(CreditAllowed) [Grand Total] from Customer
) T
group by GroupID,T.[Grand Total]

If it solves your purpose then mark this at your answer.

Regards
Varun

Posted 27-Apr-17 19:33pm

Varun Sareen

Updated 30-Apr-17 22:40pm

v2

Comments

Member 13143008 28-Apr-17 3:03am

@Varun Sareen no sir it didnt work it give an syntax error

Varun Sareen 1-May-17 4:39am

Try this:-

Select GroupID,SUM(CreditAllowed) [Sub Total],T.[Grand Total]
from Customer
Outer apply
(
Select SUM(CreditAllowed) [Grand Total] from Customer
) T
group by GroupID,T.[Grand Total]

Member 13143008 1-May-17 10:09am

@Varun Sareen thank you sir, but it also didnt work. i want this from in php language not only in mysql

Member 13143008 1-May-17 10:11am

@Varun Sareen
these are th errors
10 errors were found during analysis.

Unexpected character. (near "[" at position 32)
Unexpected character. (near "]" at position 42)
Unexpected character. (near "[" at position 46)
Unexpected character. (near "]" at position 58)
An alias was previously found. (near "Sub" at position 33)
An alias was previously found. (near "Total" at position 37)
An alias was previously found. (near "]" at position 42)
Unrecognized keyword. (near "Outer" at position 76)
Unexpected token. (near "apply" at position 82)
Unexpected token. (near "(" at position 89)

SQL query: Documentation

Select groupId,SUM(CreditLimit) [Sub Total],T.[Grand Total] from customer Outer apply ( LIMIT 0, 25

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[Sub Total],T.[Grand Total]
from customer
Outer apply
( LIMIT 0, 25' at line 1

Varun Sareen 2-May-17 0:20am

sorry dear, i have no idea in php language..but still if i found any solution; i will definitely get it back to you.

Regards
Varun

Member 13143008 2-May-17 1:17am

thank you so much sir

Sub total and grand total

1 solution

Solution 1

Add your solution here

Preview 0

Sub total and grand total

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us