SQL: Grouping by two colums and sum another

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP

SQL: Grouping by two colums and sum another



I am trying to sum up the cost column and then group by the type and then the date column. It should be very straight forward but after I apply the sum aggregate and try to group by the two columns I keep ending up with either too few or two many results and the summed total is wrong in each case.



If I have a table as such below:




<table >
<tbody>
<tr>
<td> Date</td>
<td> Type</td>
<td> Cost</td>
</tr>
<tr>
<td> Jan 01</td>
<td> TV</td>
<td> 20</td>
</tr>
<tr>
<td> Jan 01</td>
<td> TV</td>
<td> 25</td>
</tr>
<tr>
<td> Jan 01</td>
<td>Phone </td>
<td> 10</td>
</tr>
<tr>
<td> Jan 01</td>
<td> Phone</td>
<td> 20</td>
</tr>
<tr>
<td> Jan 02</td>
<td> TV</td>
<td> 30</td>
</tr>
<tr>
<td> Jan 02</td>
<td> Phone</td>
<td> 80</td>
</tr>
<tr>
<td> Jan 02</td>
<td> Phone</td>
<td> 20</td>
</tr>
<tr>
<td> Jan 03</td>
<td> TV</td>
<td>20 </td>
</tr>
<tr>
<td> Jan 03</td>
<td> TV</td>
<td> 40</td>
</tr>
<tr>
<td> Jan 03</td>
<td> Other</td>
<td> 10</td>
</tr>
</tbody>
</table>



What is the proper group by function to get the output equivalent to:




<table >
<tbody>
<tr>
<td> Date</td>
<td> Type</td>
<td> Cost</td>
</tr>
<tr>
<td> Jan 01</td>
<td> TV</td>
<td> 45</td>
</tr>
<tr>
<td> Jan 01</td>
<td>Phone </td>
<td> 30</td>
</tr>
<tr>
<td> Jan 02</td>
<td> TV</td>
<td> 30</td>
</tr>
<tr>
<td> Jan 02</td>
<td> Phone</td>
<td> 100</td>
</tr>
<tr>
<td> Jan 03</td>
<td> TV</td>
<td>60 </td>
</tr>
<tr>
<td> Jan 03</td>
<td> Other</td>
<td> 10</td>
</tr>
</tbody>
</table>



I am trying to sum the 'Cost' column but I keep ending up with incorrect results



* Edit *
The accepted answer is correct, but what was going wrong for me referred to two other columns which needed to be filtered beforehand to get the expected output.




1 Answer
1



You can try group by and sum function.


group by


sum



When you use an aggregate function, you need to add the non-aggregate columns in group by.


group by


SELECT date,type,Sum(Cost) Cost
FROM T
group by date,type



sqlfiddle



[Results]:


| date | type | Cost |
|--------|-------|-----------|
| Jan 01 | Phone | 30 |
| Jan 01 | TV | 45 |
| Jan 02 | Phone | 100 |
| Jan 02 | TV | 30 |
| Jan 03 | Other | 10 |
| Jan 03 | TV | 60 |






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

Executable numpy error

Image
Clash Royale CLAN TAG #URR8PPP Executable numpy error I'm trying to create an executable file for my code. I've already tried with cx_Freeze and with pyinstaller. Both gives me the same error, which is: "Missing required dependencies 0.format(missing_dependencies))" PS C:UsersGustavoDesktopbuildexe.win32-3.6> python AgendaOficial.py C:UsersGustavoAppDataLocalProgramsPythonPython36-32python.exe: can't open file 'AgendaOficial.py': [Errno 2] No such file or directory PS C:UsersGustavoDesktopbuildexe.win32-3.6> .AgendaOficial.exe Traceback (most recent call last): File "C:UsersGustavoAppDataLocalProgramsPythonPython36-32libsite-packagescx_Freezeinitscripts__startup__.py", line 14, in run module.run() File "C:UsersGustavoAppDataLocalProgramsPythonPython36-32libsite-packagescx_FreezeinitscriptsConsole.py", line 26, in run exec(code, m. dict ) File "AgendaOficial.py", line 8, in File "C:UsersGustavoAppDataLocal...
Read more

Trying to Print Gridster Items to PDF without overlapping contents

Image
Clash Royale CLAN TAG #URR8PPP Trying to Print Gridster Items to PDF without overlapping contents I want to print gridster items to pdf using chome browser. Unfortunately the item overlaps on next page. Is there any way to make gridster item go to next page if it doesnt fit in the current page when printing to pdf? Screenshot By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Read more

Mass disable jenkins jobs

Image
Clash Royale CLAN TAG #URR8PPP Mass disable jenkins jobs Is it possible to disable a large number of Jenkins jobs in one go? I have a large number of jenkins job I need to disable. Visiting each job in turn and disbling is tedious. 2 Answers 2 https://wiki.jenkins-ci.org/display/JENKINS/Configuration+Slicing+Plugin That works beautifully install it go to http://your_jenkins.com:8080/slicing/jobdisabledbool/? By using configuration slicing plugin: By using this plugin easy to find out anything has enabled or disable jobs and timer separated jobs Manage Jenkins >Config slicing > select options By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Read more