I have a form content like this - mysql

I have a problem when it will create a view in phpmyadmin
like this # 1349 - SELECT Views contains subqueries in the FROM clause
my coding like this please help
SELECT b.ngr_tjuan, SUM (b.LFormal) AS LFormal, SUM (b.PFormal) AS PFormal, SUM (b.LFormal) + SUM (b.PFormal) AS SUMFormal,
SUM (b.LInformal) AS LInformal, SUM (b.PInformal) AS PInformal, SUM (b.LInformal) + SUM (b.PInformal) AS SUMInformal FROM
(SELECT a.ngr_tjuan,
(CASE WHEN a.sktor_pkrjaan = 'FORMAL' AND a.jk = 'L' THEN JJK ELSE 0 END) AS LFormal,
(CASE WHEN a.sktor_pkrjaan = 'FORMAL' AND a.jk = 'P' THEN JJK ELSE 0 END) AS PFormal,
(CASE WHEN a.sktor_pkrjaan = 'INFORMAL' AND a.jk = 'L' THEN JJK ELSE 0 END) AS LInformal,
(CASE WHEN a.sktor_pkrjaan = 'INFORMAL' AND a.jk = 'P' THEN JJK ELSE 0 END) US PInformal
FROM
(SELECT ngr_tjuan, COUNT (jk) AS JJK, sktor_pkrjaan, jk
FROM tbtki2
GROUP BY ngr_tjuan, sktor_pkrjaan, jk) a
WHERE a.JJK> 0) b
GROUP BY b.ngr_tjuan

Related

oracle SQL procedure updating table

I need to update the the following columns in a table. males,females,infants and children using SQL procedure.from this column paxtype which has f,i,m,c.which is females,infants,males and children repectively.but am not able error ORA-00907: missing right parenthesis
update xxxx a set (a.INFANTS,a.MALES,a.CHILDREN,a.FEMALES)=
(SELECT b.PAXTYPE, COUNT (b.PAXTYPE) FROM xxxx b
(case ( count (b.PAXTYPE))
when ( count (b.PAXTYPE))='M'then 'a.males'
when ( count (b.PAXTYPE))='F' then 'a.females'
when ( count (b.PAXTYPE))='I' then 'a.infants'
when ( count (b.PAXTYPE))='C' then 'a.children'
END)
WHERE a.date_key = TO_CHAR (b.FLIGHTDATE, 'RRRRMMDD')
AND a.FLTNUM_KEY = TRIM (SUBSTR (b.flightnumber, 3))
AND a.origin = b.frm
AND a.destination = b.too
--and a.date_key=20170801
--and fightnumber = '100'
AND TRIM (a.cancelled) IS NULL
-- and rownum = 1
GROUP BY b.PAXTYPE;
)
It looks like you want a total count of each type, right? I think this is what you want to do.
update xxxx a set (a.INFANTS,a.MALES,a.CHILDREN,a.FEMALES)=
(SELECT
sum(case when b.PAXTYPE = 'I' then 1 else 0 end) as infants_count,
sum(case when b.PAXTYPE = 'M' then 1 else 0 end) as males_count,
sum(case when b.PAXTYPE = 'C' then 1 else 0 end) as children_count,
sum(case when b.PAXTYPE = 'F' then 1 else 0 end) as females_count
FROM xxxx b
WHERE a.date_key = TO_CHAR (b.FLIGHTDATE, 'RRRRMMDD')
AND a.FLTNUM_KEY = TRIM (SUBSTR (b.flightnumber, 3))
AND a.origin = b.frm
AND a.destination = b.too
--and a.date_key=20170801
--and fightnumber = '100'
AND TRIM (a.cancelled) IS NULL
-- and rownum = 1
GROUP BY b.PAXTYPE);

mySQL Sum in the sum column

SELECT tbl_trans.trans_username,
Sum(Case When tbl_trans.trans_type <> 'REWARD' AND tbl_trans.trans_winlose <> 'QB' then
trans_winloseamount Else 0 End) BETHOST,
Sum(Case When tbl_trans.trans_winlose = 'QB' then
trans_winloseamount Else 0 End) QB,
Sum(Case When tbl_trans.trans_type = 'REWARD' then
trans_winloseamount Else 0 End) reward ,
Sum(sum(BETHOST)+sum(QB)+sum(reward)) totalsum
FROM tbl_trans
GROUP BY trans_username
i need to totalsum of BETHOST,QB and reward
how to sum of this Sum(sum(BETHOST)+sum(QB)+sum(reward)) totalsum
pls try this
sum(SELECT(BETHOST))+sum(SELECT(QB))+sum(SELECT(reward)) AS totalsum

Is it possible to use SUM - CASE - IF together?

When I try to use this:
SUM(CASE WHEN IF(SUM(CASE WHEN "b.count_students_status" = 1 THEN 1 ELSE 0 END) >= 1, 1, 0) = 1 THEN 1 ELSE 0 END)
It says Query Error: Improper usage of Group Function
Below is the complete code:
SELECT
"a.batch" AS Batch,
SUM(
CASE
WHEN IF(
SUM(
CASE
WHEN "b.count_students_status" = 1
THEN 1
ELSE 0
END
) >= 1,
1,
0
) = 1
THEN 1
ELSE 0
END
) AS Payments_Not_Received
FROM
"DBU - Complete"
WHERE "a.suspended" = 'no'
GROUP BY "a.batch"
I wanted to convert the status to 1, if there are multiple occurrences and then sum the total occurrences.
Any help please - I am using ZOHO to build the query?
You're trying to reference the result of the GROUP BY before is has run. Try something like this:
select t.BATCH,
sum(case when t.pnr >=1 then 1 else 0 end) as Payments_Not_Received
from
(
select
a.batch as BATCH,
SUM(CASE WHEN "b.count_students_status" = 1 THEN 1
ELSE 0
END) as pnr
from yourTable a
WHERE a.suspended = 'no'
group by a.batch
) t
group by t.BATCH;

How could I check the value of an aggregation function inside the same query?

This MySQL query gives me this error 'Unknown column 'winnings' in 'field list'
SELECT
o.user_id,
sum(case when o.result = 1 or o.result=2 or o.result = 0 then 1 else 0 end) as tahmins_no,
sum(case when o.result = 1 then 1 else 0 end) as winnings,
sum(case when o.result = 2 then 1 else 0 end) as loses,
sum(case when winnings = 10 then 0.5 else 0 end) as counter
FROM `odds_tahminler` o
I know that winnings is the value of the sum() aggregation function, But is there any way to check the winnings value within the query?
You can't use an aggregated column inside the select. However you can use a subquery to obtain the counter value after all the aggregated columns have been computed.
How is the counter value calculated? I assumed that the counter should be (winnings - 10) / 2 if there's at least 10 winnings and 0 otherwise. In that case you can obtain it with this query
SELECT O.*,
GREATEST( (O.winnings - 10) / 2, 0) as counter
FROM
(
SELECT u.username,
o.user_id,
sum(case when o.result = 1 or o.result=2 or o.result = 0 then 1 else 0 end) as tahmins_no,
sum(case when o.result = 1 then 1 else 0 end) as winnings,
sum(case when o.result = 2 then 1 else 0 end) as loses
FROM `odds_tahminler` o
) as O
you can try:
SELECT
o.user_id,
sum(case when o.result = 1 or o.result=2 or o.result = 0 then 1 else 0 end) as tahmins_no,
sum(case when o.result = 1 then 1 else 0 end) as winnings,
sum(case when o.result = 2 then 1 else 0 end) as loses,
sum(case when winnings = 10 then 0.5 else 0 end) as counter
FROM `odds_tahminler` o
GROUP BY o.user_id
HAVING counter>2

Getting total value from each field?

How do I get the Total value of Yes, No, Other fields of each username?
I like to add Total field.
SELECT Username,
SUM(CASE WHEN type = 'Yes' THEN 1 ELSE NULL END) as Yes,
SUM(CASE WHEN type = 'No' THEN 1 ELSE NULL END) as No,
SUM(CASE WHEN type = '' THEN 1 ELSE NULL END) as Other
//How to get total of Yes/No/Other
FROM table
WHERE source = 'CompanyName' ";
Also the highest Total goes at the top order.
use 0 instead of NULL, add the missing group by and use COUNT(*) to get the total of each group and order the result:
SELECT Username,
SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other,
COUNT(*) as TOTAL
FROM table
WHERE source = 'CompanyName'
group by Username
order by TOTAL desc;
This assumes that type can only be 'Yes', 'No' or ''.
Use a sub query to sum up your results and add a sort:
select yes, no, other, yes + no + other as Total
from (
SELECT Username,
SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other
FROM table
WHERE source = 'CompanyName'
)
order by (yes + no + other) desc
Don't use SUM(null), the SUM of (1,1,1,null) = null, not 3.
SELECT s.*, s.yes+s.no+s.other as all FROM (
SELECT Username,
SUM(CASE WHEN type = 'Yes' THEN 1 ELSE 0 END) as Yes,
SUM(CASE WHEN type = 'No' THEN 1 ELSE 0 END) as No,
SUM(CASE WHEN type = '' THEN 1 ELSE 0 END) as Other
FROM table
WHERE source = 'CompanyName'
GROUP BY Username
) s
ORDER BY all DESC

Resources