Jump to content

Search the Community

Showing results for tags 'group by'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Calendars

  • Community Calendar

Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 4 results

  1. relational algebra

    hi, sorry, i have a question to do...but i know how to write in sql query ....however the answer need to write on relational algebra in query is like : SELECT * FROM operation where r='broken' group by pc having count(pc) >1 ; π id,pc,result ( σ r = 'broken' (operation)) like group by and having clasue...i no idea ... i searched on internet still @@ .... isnt the answer like that, i using split/step by step method to do rel 1 = σ r = 'broken' (operation) rel 2 = σ count(pc)>1 γ pc,count(pc) (rel 1)) rel 3 = π id,pc,result (rel 2 ) thanks
  2. hi there, the following query was developed and works as it should. SELECT ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC; # Order by Total, then Supplier Name took about a day to develop this inbetween other work. it returns the correct data. the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took about an hour inbetween other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took approximately another hour between other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv once again - the data does not corroborate with the first csv, the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied? the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.
  3. Distint Records

    I have the following 'status' table which gets updated with a new record at each step of the process. I need a view to return all records with the latest transdate for each prno. Status TransDate PRNo1 2012-01-31 16:19:56.000 11 2012-02-01 10:30:36.000 20 2012-02-01 10:45:01.000 31 2012-02-08 12:30:17.000 42 2012-02-09 12:30:17.000 413 2012-02-15 11:47:42.000 43 2012-02-15 12:50:15.000 4 View needs to return the following Status TransDate PRNo1 2012-01-31 16:19:56.000 11 2012-02-01 10:30:36.000 20 2012-02-01 10:45:01.000 33 2012-02-15 12:50:15.000 4 Some wizardry or quidance would be much appreciated.Stu
  4. Group By Or Case ?

    I need some help being a newbie to this game...Running SQL 2008 on W2003. Source table contains the following fields.Name Department Status---- ---------- ------Stuart IT 1Kate IT 4Marc Finance 8Mick Quality 6Stuart IT 8 Report needs to return the count of the status field for each department depending on the status range of numbers, so... status 1 to 8 = 'submitted'status 7 to 8 = 'realised'Thus the report of the above table should look like: Department Submitted Realised---------- --------- --------IT 3 1Finance 1 1Quality 1 0 Using a GROUP BY I can get half of the report but not all of it.Any suggestions would be appreciated. Cheers,Stu
×