|
Hi Guys,
I have a condition that, I have to display data for all states with 3 different conditions like
i have a state column and i have to calculate count for judicial, non-judicial , and redemption these columns i have to display for each state how can i make it happen in a single query with good performance.
I have 3 different conditions to get count, so please help me
ex
<table border="1" cellpadding="3">
<tr>
<th>
State
</th>
<th>
Judicial
</th>
<th>
Non-Judicial
</th>
<th>
Redumtion
</th>
</tr>
<tr>
<td>
AZ
</td>
<td>
10
</td>
<td>
01
</td>
<td>
0
</td>
</tr>
<tr>
<td>
CA
</td>
<td>
12
</td>
<td>
94
</td>
<td>
04
</td>
</tr>
<tr>
<td>
NV
</td>
<td>
32
</td>
<td>
04
</td>
<td>
04
</td>
</tr>
<tr>
<td>
QA
</td>
<td>
32
</td>
<td>
49
</td>
<td>
04
</td>
</tr>
</table>
|
|
|
|
|
Fix your formatting.
What is your table structure?
In general, you should be able to do that using GROUP BY clause and SUM() aggregate function.
|
|
|
|
|
As Shameel said, something like this should work:
SELECT
State,
Sum(Case When TheColumn = 'Judicial' Then 1 Else 0 End) As JudicialCount,
Sum(Case When TheColumn = 'Non-Judicial' Then 1 Else 0 End) As NonJudicialCount,
Sum(Case When TheColumn = 'Redemption' Then 1 Else 0 End) As RedemptionCount
FROM
YourTable
GROUP BY
State
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you Richard Deeming. i written query like what you have written, is this style of query takes less execution time ?
|
|
|
|
|
VishwaKL wrote: is this style of query takes less execution time ?
Less than what? You'd need to measure the performance of this query against the query you want to compare it to.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Welcome to the database forum; as you have noticed, it focuses on SQL and databases. Are you by any chance looking for an XPath-query? 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I want to write a procedure
1.find the child of a parent recursively till we do not find any child.
2.Then collect all the child in get in step one and update the value of name of all the child-->second procedure
approach:
1. write a procedure to take all the parent as input
2. write a procedure to find child recursively till we do not find any child further
e.g
1:parent
1.1 child
now consider 1.1 as parent and find child e.g 1.2
recursively call and collect and then update all the child name
proc 2: simple procedure with update where id ("child we got previously ")
I got the logic but do not know how to call recursively the procedure and hold the values of child
any sample code or input is highly appreciated!!!
Thanks,
Ramina
|
|
|
|
|
ramina sen wrote: any sample code or input is highly appreciated!!!
MSDN[^], see the heading "Using a recursive CTE in an UPDATE statement". More info, also on MSDN[^]
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Is there some reason for doing this recursively, you should be able to achieve a simple update in one sql statement using an inner join!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
correct,
but as I have mention my inner query is giving me only parent but I want to make use of the result of the inner query again as input till I did not find any child ,so I am using procedure as I have to collect all the parent I get from the inner query and then update the outer query using the inner query result.it is the requirement what I want to achive,
e.g
update table A
set A.a as "xyz"
//here I will get t
where A.id in
(
select A.child_id from A,B,C
--I get the child by passing parent as input and again consider the child as parent
and find the child
where A.id= b.id
b.name = x and A.prent_id = ?
//few join condtion here
)
e.g
input 1 result 1.1
input 1.1 result 1.1.1
input 1.1.1 result 1.1.1.1
update A
where A.id in
('1.1','1.1.1','1.1.1.1')--here I want to use a container and make use of it
Hope you get my intention now ..Thanks for your response.
|
|
|
|
|
can you plz explain me join query and all types of join with simple example so that i can use it very well..
thanks and regards
rushabh parekh
|
|
|
|
|
|
|
|
I am new at this.
I made a Access Database that produces a report that via an entered date range, displays data contained in the table when a user enters a Start Date and End Date to pull up the report.
The report works fine; providing dates, time, caller names, locations, and a few other statistics.
The Table has a field where the user notes the time a call is made(10:16).
I need to enable the report to break down all calls in that field and count them in one of the three categories: (4:30 to 6:29), (6:30 to 8:29), and 8:30 to 10:30)
I will look something like this on the report: (Where n is the total count of calls received within these three sequential time frames.
Call Count 4:30 to 6:29 (n)
Call Count 6:30 to 8:29 (n)
Call Count 8:30 to 10:30 (n)
|
|
|
|
|
Caveat I have not worked with Access for a decade or more 
I would add a calculated field to the table/view called ShiftNo - function is obvious. Calculate the shift and then group the report by the ShiftNo. Your query changes from a simple select to a grouped query. (not sure what the terminology is these days in Access)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello i am Rodney and i have a problem on how should i arrange the seatmap database.
i have id, busNumber, from, to, seatId, column, reserveStatus, firstname,lastName, paid, tripStatus(finish/not).
i have made this working but the thing that im talking about multiple reservation from Point A to Point E, then there is substation of B,C, and D. What i have in mind is assuming there are 3 stopovers, should i just add columns in my seatMap table, like (stop1 = stopover1) stop1Firstname,stop1LastName,stop1ReserveStatus,stop1Paid,stop1TripStatus.
Thats what i have in mind but is there any better way solving these? or should i make new tables for stopover1 stopover stopover3 if it is so could u please guide me, i mean i dont have that much of an idea regarding to that. Thanks a lot. Sorry for my english.
|
|
|
|
|
|
hello, thanks a lot. anyway, so it is much better to make new tables per stopovers right? for example buses have stopovers i.e PointA - Point D, which means it has stopover of Stopover B and Stopover C. In my mind is that a table for the origin and destination, reservation (id,busNum,reserveStatus,seatId,seatColuumn,fullName,date)
and for the stopovers(id,busNum,date,stopoverName,seatId,seatColumn,reserveStatus,fullName). Could you please guide me for a better way or if this would do? Thanks a lot
|
|
|
|
|
To be able to design a database correctly, one has to understand the users' requirements and their business rules. Many considerations arise, such as:
1. Can a passenger start and end his journey at any stopovers?
2. How to ensure a seat in a bus is not reserved by more than one passengers for a journey on the same date and time.
Based on the limited info that you have given and my own assumption, I have drafted a preliminary database schema for your reference and adaptation, you will have to work out your final solution yourself:
Table: reservation
reserveID (PRIMARY KEY),
busID,
reserveStatus,
custID (Foreign Key to custID in customer table),
start_stopoverID,
end_stopoverID,
[other fields]
Table: customer
custID (PRIMARY KEY),
custName,
[other fields]
Table: bus
busID (PRIMARY KEY),
busNum,
[other fields]
Table: stopovers
stopoverID (PRIMARY KEY),
stopoverName,
[other fields]
Table: bus_stopovers
stopoverID (PRIMARY KEY),
busID (PRIMARY KEY),
[other fields]
|
|
|
|
|
Thank you so much for the draft. I will work for it. i hope i could do these multiple seatmap. i need it do be done soon. Thanks a bunch.
|
|
|
|
|
Have a look at this[^] schema. It's for an Airline, but I believe you can reuse most of the info.
|
|
|
|
|
what is criteria for a SCN number change ? Means when it changes and when it not changes ?
|
|
|
|
|
|
hi...Im newly member..please help me settle this
server: Ubuntu 12.04 LTS
database use postgresql 9.1 with ebikko engine
for edms system
after transfering from the actual server with is vm worstation
to another vm worstation and change the ip..the system cannot been log in..assume some sort of hard code that need to change but does not know where.
How to solve this?
Guide me somebody
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
