I would like the following pivot query to show value 0 instead of null, Output for the above is, Employee id 122 does not have a Repayment value so the desired output is, dbfiddle Answer I don’t see the need for the second branch of the repayment case. If you want 0 when the category is not available, just else
Tag: pivot
SQL – Database table mapper – Insert into table, based on select and joins
This is a life example of the question so you can understand better what we need. EXAMPLE We have 3 tables For each car item we want to keep these data: And 3 ‘spec’ values located in ‘specs’ table, based on ‘car_spec’ table: We want to combine all the required data into one table like this. Sample data cars table
SQL query for report by name with many columns count by status
Is it possible by sql query to produce a report group by a name with count by status. Tables: Project (id, name, status_id, service_id) Status (id, name) Service (id, name) I need to make a report, count by status group by service: Columns results: Service name, status name 1 , status name 2, status name 3, total Rows results: Answer
How to calculate percentage of availability using 2 sql query and combining it into result
I have to create a service availability using info from this 2 query. First Query. example result second query example result The formula as below ((Reporting Period – Unplanned Outage)/Reporting Period)*100 Answer Consider using conditional aggregation: The availability computation could probably be simplified:
Can’t Insert into seperate column while using two select statements
TABLE: NAME : Jance C hiatt, Sam, Brook, Newyork, London ADDRESS : NULL,NULL,NULL,NULL NewYork and London should be address column but it is showing on Name itself Here address is not coming at the separate column of database. Kindly tell me what issue I am facing Answer Each insert statement creates new rows, so you cannot expect your queries to
Remove zero amount in a query
I have a table finalData with the following columns and data I am creating mysql query to display the income and expenses of Singapore per Area, but I have to exclude areas that has zero amount. Here’s the code I tried: Output should be like this Any help would be appreciated. Thanks Answer This logic would be simpler phrased with
Division of 2 values in mysql
Hello I’m a newbie in SQL. I have a table of workers and I want to calculate the avg num of female workers. I want to divide 2 tables that I create, but when I do the division the value of the new …
Multiple columns from one column
I track the aging of customer invoices. The below code example returns the balance of customer invoices by customer that are between 0 and 30 days old. However, I want to run one query that pulls separate columns into Excel for each customer’s balance aged between 0 and 30 days, 31 and 60, 61 and 90, and finally over 90
SQL sort and count duplicate value
I need to count and sort student, how many course they took based on level like this: ID|NAME |Point1|point 2|point 3| 1 | Tom | 3| 3 | 1 | 2 | Jer | 5| 0 | 6 | I have 3 table …
how to get the count of data based on status id in sql
i have following table id statusid 100 1 100 2 100 3 101 1 101 3 i am getting the result like following id data1 data2 data3 100 1 1 1 101 …