Jump to content

gabucknall

New Members
  • Content Count

    9
  • Joined

  • Last visited

Community Reputation

0 Neutral

About gabucknall

  • Rank
    Newbie
  1. I have a mySQL database that records visits by clients to an advice centre. Various demographic data is gathered including ethnicity. The data is stored in a table 'client' which contains (amongst other fields) clientid and ethnicid. A client can visit any number of times. Their visits are stored in an 'advice' table which contains fields adviceid, clientid, advice date. I wish to extract a table showing totals of each ethnicity during a date period but each client should count only once no matter how many times they visit. The code I am using is: SELECT COUNT(DISTINCT client.clientid), COUNT(DISTINCT CASE WHEN client.ethnicid='5' THEN client.clientid END) AS wh, COUNT(DISTINCT CASE WHEN client.ethnicid='6' THEN client.clientid END) AS EN, COUNT(DISTINCT CASE WHEN client.ethnicid='7' THEN client.clientid END) AS ir, COUNT(DISTINCT CASE WHEN client.ethnicid='8' THEN client.clientid END) AS ow, COUNT(DISTINCT CASE WHEN client.ethnicid='9' THEN client.clientid END) AS bb, COUNT(DISTINCT CASE WHEN client.ethnicid='10' THEN client.clientid END) AS ca, COUNT(DISTINCT CASE WHEN client.ethnicid='11' THEN client.clientid END) AS af, COUNT(DISTINCT CASE WHEN client.ethnicid='12' THEN client.clientid END) AS so, COUNT(DISTINCT CASE WHEN client.ethnicid='13' THEN client.clientid END) AS ob, COUNT(DISTINCT CASE WHEN client.ethnicid='14' THEN client.clientid END) AS aab, COUNT(DISTINCT CASE WHEN client.ethnicid='15' THEN client.clientid END) AS ban, COUNT(DISTINCT CASE WHEN client.ethnicid='16' THEN client.clientid END) AS oas, COUNT(DISTINCT CASE WHEN client.ethnicid='17' THEN client.clientid END) AS chi, COUNT(DISTINCT CASE WHEN client.ethnicid='100' THEN client.clientid END) AS ind, COUNT(DISTINCT CASE WHEN client.ethnicid='101' THEN client.clientid END) AS pak, COUNT(DISTINCT CASE WHEN client.ethnicid='102' THEN client.clientid END) AS mix, COUNT(DISTINCT CASE WHEN client.ethnicid='18' THEN client.clientid END) AS oth, COUNT(DISTINCT CASE WHEN client.ethnicid='19' THEN client.clientid END) AS nk, COUNT(DISTINCT CASE WHEN client.ethnicid='99' THEN client.clientid END) AS na FROM advice INNER JOIN client ON client.clientid=advice.clientid followed by some 'where' parts to limit the date range. This doesn't seem to be giving the correct results and clients are being counted more than once. (I understand that this might not be the most elegant code but I am fairly new to this.) What am I doing wrong? Thanks.
  2. Thank you. Sorry about the mix up - there is extra fields in the query but I had removed them in my editing. I will try this and let you know.
  3. SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN expenses ON matters.matterid=expenses.matterid WHERE client.clientid = 238 It was there but needed scrolling right. Have removed the code tags fromat so it's all viewable. SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN expenses ON matters.matterid=expenses.matterid WHERE client.clientid = 238
  4. Sorry - I copied the wrong piece of text. The query should be: SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN expenses ON matters.matterid=expenses.matterid WHERE client.clientid = 238 What I was saying was that I want the actions and expenses on separate lines from one query but they appear together.
  5. I have a mySQL database with some of the tables and fields being: CLIENT - clientid, client MATTERS - matterid, clientid, refno, nickname MATTERJUNACTIONS - matterid, notes, fee, actiondate EXPENSES - expensid, matterid, expensedate, amount A client can have many matters and each matter can have many actions and a number of expenses. I want to produce a report which has a separate row for each action or expense together with matter and client details. The report column headers would be: clientid | client | refno | nickname | actiondate | note | fee | expensedate | amount | I have a query: SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN expenses ON matters.matterid=expenses.matterid WHERE client.clientid = 238 This produces a report with a number of rows but doesn't separate the rows with expenses on a different row to the actions. I get something like: 238 | ABC Ltd | 1245 | matternickname | 2018/4/1 | this is the action note | 50 | 2018/4/15 | 25 | Rather than 238 | ABC Ltd | 1245 | matternickname | 2018/4/1 | this is the action note | | | | 238 | ABC Ltd | 1245 | matternickname | | | 50 | 2018/4/15 | 25 | What am I doing wrong?
  6. Thank you. I will try that and let you know.
  7. The dates would be those in the table not a regular set of dates. I've only tried the simple query - SELECT fullname, registerdate, present FROM register - as I've no idea how to get started.
  8. am developing a registering system as part of a mySQL database. The relevant table is called register and has as some of its fields - fullname (ie the name of the attendee), present (answered either Y or N), registerdate (the date of the lesson) and projectcode (links to a project table - in this system a project is a course). I want to get out a report which has the lesson dates as the column headers and the student names as the row headers so it looks something like: |1/3/2018 | 6/3/2018 | 12/3/2018 | etc Adam |Y | N | Y | Sue |Y | Y |Y and so on. I have a simple query as below SELECT fullname, registerdate, present FROM register Just gets me a list. I have looked at lots of pivot table examples but none have a date as the X axis headers. The lessons are irregular and aren't on various dates/days of the week. Can anyone offer some help please?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.