How to find out the maximum sales of an item for each sales man. The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items. here I have to find out for every sales man, which item he sold maximum
RE: How to find out the maximum sales of an item for e...
First, you have to ask two questions -
Assuming the given table is (sp, item, Q) --- sp is sales person id, Q is quantity sold for that item.
a) are there duplicate rows, which means we will need to sum the quantity first. b) what happens if a sales person sold more than one item with the SAME maximum quantity ? ie. John's top sale was 500, however, he sold 500 computers and also sold 500 laptops. Does the report show only one item, or, both items, and, if only one, then which one should be shown (ie. what is the criteria). I am assuming that both items should be shown.
Here is the query
select sp, item, sum(Q) as TQ from <table> into #t group by sp, item
select sp, item, TQ from #t a where a.TQ = (select max(TQ) from #t b where b.sp = a.sp)
========
if you want everything in one query, then here is the one --
select a.sp, a.item, a.TQ from (select sp, item, sum(Q) as TQ from <table> group by sp, item ) a where a.TQ = (select max(TQ) from #t b where b.sp = a.sp)
RE: How to find out the maximum sales of an item for each sales man.The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items.here I have to find out for every sales man, which item he sold
Assuming that both the items are required to be shown if two or more items are sold of same quantity. The query is :-
select sales_man,item,sales from table where (sales_man,sales) in (select sales_man,max(sales) from table group by sales_man);
There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?
Latest Answer : But this will remove all the db constraints from the table.... am I right...so we can do the same like as follows:select * into temp_1 from Original ---so no constraints are droppedtruncate table Originalinsert into Originalselect distinct * from temp_1drop ...
Latest Answer : Live table will be created using:
1.
Create table
(
column name datatype
)
2.
Select * into from
e-x: select * into new_emp from emp
Temp Table will be created using:
1.
Create table <#table name>
(
column name datatype
) ...
Latest Answer : The table in Sybase can be deleted by using the command drop table. The syntax of this command is as below:drop table Then the window waits for entering the table name where the table name to be deleted in Sybase is entered and go is entered which results ...
Latest Answer : Partition is a old technology helps to access data faster (not actually faster) in almost all the databases. Sybase ASE 15.0 opens up a new possiblity of this. Sybase ASE contains semantic Partioning which is not available in Oracle latest version called ...
A column name is given to you. You have to find out which table has that column. Database has 230 tables. For example: empid (column name) For this how do you find out which table has the column (empid) ?
How to find out the maximum sales of an item for each sales man.The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items.here I have to find out for every sales man, which item he sold maximum
Using XMLType for Handling XML Data in the Database Being an object type XMLType can not only be used to store XML data in the database but also to operate on that data via its built in methods Regardless of the storage model you choose XMLType provides a set of XML specific methods to operate on XM
Using Oracle Database for Storing Modifying and Retrieving XML Data With Oracle XML DB you have various XML storage and XML processing options allowing you to achieve the required level of performance and scalability One of the most interesting things about Oracle XML DB is that it allows you to per
Performing XSLT Transformations inside the Database Now that you have the employees XSL stylesheet stored in the database and the xmlusr schema is permitted to access the hr employees table you can create a script that will instruct the database to build an HTML page based on the data stored in hr e
Moving All the XML Processing into the Database In the preceding example the database server performs only a part of the XML processing while the rest is still performed by the PHP engine Specifically the database server generates an employees XML document based on the records from the hr employees
Performing XML Processing inside the Database When building XML enabled applications on top of Oracle there are many advantages to performing the XML processing inside the database when compared to performing it on the client The key advantages to perform XML processing inside the database are as fo
ODP NET Populating a Dataset with a Single Data Table A dataset is simply a group of data tables These data tables can be identified with their own unique names within a dataset You can also add relations between data tables available in a dataset mosgoogle The following code gives you the details o
jQuery Table Row Finished Code The Finished Code Our second example page has demonstrated table row striping highlighting tooltips collapsing expanding and filtering Taken together the JavaScript code for this page is mosgoogle geshibot lang php" document ready function var highlighted
jQuery Expanding and Collapsing Table Rows The expanding and collapsing behavior added earlier also conflicts with our filters If a section is collapsed and a new filter is chosen then the matching items are displayed even if in the collapsed section Conversely if the table is filtered and a section
jQuery Table Row Filtering Earlier we examined sorting and paging as techniques for helping users focus on relevant portions of a table s data We saw that both could be implemented either with server side technology or with JavaScript Filtering completes this arsenal of data arrangement strategies B
jQuery Collapsing and Expanding Table Rows When large sets of data are grouped in tables as each year s set of articles are in our News page collapsing or hiding a section s contents can be a convenient way to get a broad view of all of the table s data without having to scroll so much mosgoogle To
